Dublin Power BI User Group

Did you know the Microsoft Product Team will be at Power Platform Summit Europe in full force presenting the Microsoft Power Series track – with over 15 hours of deep dive, hands on content – and the PowerBIUG is giving away one free personalized Power Pack?!

The Power Pack includes a free event pass to 2019 Power Platform Summit Europe, a Power BI t-shirt, User Group stickers, AND 30 minutes one-on-one with the Microsoft Power Series presenter of your choice. Ask questions, get ideas for your latest project, and give feedback to shape the future of Power BI.

To be entered to win please reply to this group post with your latest Power BI challenge, a recent dashboard/reporting win, or a Power BI enhancement idea. The winner will be chosen from the local User Group with the most discussion posts. The local winning PUG will also receive a feature on the PowerBIUG home page, a blog highlighting our plans for 2019, and social buzz.

The Microsoft Power Series will include sessions such as:

• AI in Power BI Service: Data Flow and Machine Learning
• Debugging common DAX and Model performance issues
• Advanced Data Visualization & Storytelling with Power BI Desktop
• And so much more – view the entire lineup here!

Register today with this 10% off UG coupon (Coupon Code: EUPSummit10) and take advantage of advanced registration savings of €200 ending February 28.

Did you know the Microsoft Product Team will be at Power Platform Summit Europe in full force presenting the Microsoft Power Series track – with over 15 hours of deep dive, hands on content – and the PowerBIUG is giving away one free personalized Power Pack?!

The Power Pack includes a free event pass to 2019 Power Platform Summit Europe, a Power BI t-shirt, User Group stickers, AND 30 minutes one-on-one with the Microsoft Power Series presenter of your choice. Ask questions, get ideas for your latest project, and give feedback to shape the future of Power BI.

To be entered to win please reply to this group post with your latest Power BI challenge, a recent dashboard/reporting win, or a Power BI enhancement idea. The winner will be chosen from the local User Group with the most discussion posts. The local winning PUG will also receive a feature on the PowerBIUG home page, a blog highlighting our plans for 2019, and social buzz.

The Microsoft Power Series will include sessions such as:

• AI in Power BI Service: Data Flow and Machine Learning
• Debugging common DAX and Model performance issues
• Advanced Data Visualization & Storytelling with Power BI Desktop
• And so much more – view the entire lineup here!

Register today with this 10% off UG coupon (Coupon Code: EUPSummit10) and take advantage of advanced registration savings of €200 ending February 28.

Hi PUG Dublin

Thank you for sharing this opportunity with all of us. I would love to go to Amsterdam and meet the Microsoft product team .
I thought this would the  perfect opportunity to make my first knowledge share with the group and hopeful help someone out there :)
Please see below my latest dashboard/reporting win:

Pareto chart, how to resolve problem with ties and measures 

In this example the client wants to analyse the production stoppage time by  reason, being able to slice the chart by category.

As you can see right away our data have tie values for stop time (11,11;10,10).

What we would usually do:

Step #1-Create [Stop Time] measure

Stop Time := SUM('Stoppage Time'[Stop Time Hours])

Step #2 -Ranking reason based on [Stop Time]. As we want to be able to filter chart based on a slice, we nested it with ALLSELECTED

Rank1 := 
RANKX ( ALLSELECTED ( 'Stoppage Time' ), [Stop Time],, DESC, SKIP )
-- Ranking based on stop time

Step #3- Calculate cumulative value using TOPN for ALL table

Cumulative1 := 
CALCULATE ( [Stop Time], TOPN ( [Rank1], ALL ( 'Stoppage Time'[Reason] ) ) )

Step #4-  Cumulative Percentage

Pct Cumulative1 := 
   DIVIDE ( [Cumulative1], [Grand Total1] )

The outcome of the modelling that we just did, will have a number of issues, there are tied values for 2nd and 4th place, ranks are applied to Total and when plotting it to our chart, flat line will appear. In addition slicer won't work as expected.

OK, so now let's sort this out:

Step #1- In Power Query sort [Stop Time] in Descending order

Step #2- Add an Index column , this will solve the tied values problem

Step #3- Create a measure to rank the index column created above, that will be used as the basis for our next step.

Index Rank :=
RANKX (
   FILTER (
       ALL ( 'Stoppage Time' ),
       -- table to rank
       'Stoppage Time'[index] <= MAX ( 'Stoppage Time'[index] )
   ),
   LASTNONBLANK ( 'Stoppage Time'[Index], 1 ),
   ,
   ASC
)

Step #4- Calculate the Ranking using the new [Index Rank] measure

Rank2 :=
IF (
   HASONEVALUE ( 'Stoppage Time'[Reason] ), --  remove values from totals
   RANKX ( ALLSELECTED ( 'Stoppage Time' ), [Index Rank],, ASC, DENSE )
)

* note use HASONEVALUE to not show values on totals

Step #5- Calculate cumulative values using the index rank

Cumulative2 := 
CALCULATE (
  [Stop Time],
  TOPN ( [Index Rank], ALLSELECTED ( 'Stoppage Time'[Reason] ) )

Step #6- Calculate the Grand total and Cumulative %

Grand Total2 := CALCULATE([Stop Time],ALLSELECTED('Stoppage Time'))

*Note: Calculates Grand Total for All Entries Selected

Pct Cumulative2 :=
   DIVIDE([Cumulative2],[Grand Total2])​

And ta-dah, a beautiful flowy line standing tall with price in your chart. :)


and it works perfectly with the slicers too.

Surely there are other ways to make it work and this solution may not work for all scenarios, but it worked for me :)

The sharing of knowledge is power.

#Create #Develop #Model 

Did you know the Microsoft Product Team will be at Power Platform Summit Europe in full force presenting the Microsoft Power Series track – with over 15 hours of deep dive, hands on content – and the PowerBIUG is giving away one free personalized Power Pack?!

The Power Pack includes a free event pass to 2019 Power Platform Summit Europe, a Power BI t-shirt, User Group stickers, AND 30 minutes one-on-one with the Microsoft Power Series presenter of your choice. Ask questions, get ideas for your latest project, and give feedback to shape the future of Power BI.

To be entered to win please reply to this group post with your latest Power BI challenge, a recent dashboard/reporting win, or a Power BI enhancement idea. The winner will be chosen from the local User Group with the most discussion posts. The local winning PUG will also receive a feature on the PowerBIUG home page, a blog highlighting our plans for 2019, and social buzz.

The Microsoft Power Series will include sessions such as:

• AI in Power BI Service: Data Flow and Machine Learning
• Debugging common DAX and Model performance issues
• Advanced Data Visualization & Storytelling with Power BI Desktop
• And so much more – view the entire lineup here!

Register today with this 10% off UG coupon (Coupon Code: EUPSummit10) and take advantage of advanced registration savings of €200 ending February 28.

Hi PUG Dublin

Thank you for sharing this opportunity with all of us. I would love to go to Amsterdam and meet the Microsoft product team .
I thought this would the  perfect opportunity to make my first knowledge share with the group and hopeful help someone out there :)
Please see below my latest dashboard/reporting win:

Pareto chart, how to resolve problem with ties and measures 

In this example the client wants to analyse the production stoppage time by  reason, being able to slice the chart by category.

As you can see right away our data have tie values for stop time (11,11;10,10).

What we would usually do:

Step #1-Create [Stop Time] measure

Stop Time := SUM('Stoppage Time'[Stop Time Hours])

Step #2 -Ranking reason based on [Stop Time]. As we want to be able to filter chart based on a slice, we nested it with ALLSELECTED

Rank1 := 
RANKX ( ALLSELECTED ( 'Stoppage Time' ), [Stop Time],, DESC, SKIP )
-- Ranking based on stop time

Step #3- Calculate cumulative value using TOPN for ALL table

Cumulative1 := 
CALCULATE ( [Stop Time], TOPN ( [Rank1], ALL ( 'Stoppage Time'[Reason] ) ) )

Step #4-  Cumulative Percentage

Pct Cumulative1 := 
   DIVIDE ( [Cumulative1], [Grand Total1] )

The outcome of the modelling that we just did, will have a number of issues, there are tied values for 2nd and 4th place, ranks are applied to Total and when plotting it to our chart, flat line will appear. In addition slicer won't work as expected.

OK, so now let's sort this out:

Step #1- In Power Query sort [Stop Time] in Descending order

Step #2- Add an Index column , this will solve the tied values problem

Step #3- Create a measure to rank the index column created above, that will be used as the basis for our next step.

Index Rank :=
RANKX (
   FILTER (
       ALL ( 'Stoppage Time' ),
       -- table to rank
       'Stoppage Time'[index] <= MAX ( 'Stoppage Time'[index] )
   ),
   LASTNONBLANK ( 'Stoppage Time'[Index], 1 ),
   ,
   ASC
)

Step #4- Calculate the Ranking using the new [Index Rank] measure

Rank2 :=
IF (
   HASONEVALUE ( 'Stoppage Time'[Reason] ), --  remove values from totals
   RANKX ( ALLSELECTED ( 'Stoppage Time' ), [Index Rank],, ASC, DENSE )
)

* note use HASONEVALUE to not show values on totals

Step #5- Calculate cumulative values using the index rank

Cumulative2 := 
CALCULATE (
  [Stop Time],
  TOPN ( [Index Rank], ALLSELECTED ( 'Stoppage Time'[Reason] ) )

Step #6- Calculate the Grand total and Cumulative %

Grand Total2 := CALCULATE([Stop Time],ALLSELECTED('Stoppage Time'))

*Note: Calculates Grand Total for All Entries Selected

Pct Cumulative2 :=
   DIVIDE([Cumulative2],[Grand Total2])​

And ta-dah, a beautiful flowy line standing tall with price in your chart. :)


and it works perfectly with the slicers too.

Surely there are other ways to make it work and this solution may not work for all scenarios, but it worked for me :)

The sharing of knowledge is power.

#Create #Develop #Model 

Did you know the Microsoft Product Team will be at Power Platform Summit Europe in full force presenting the Microsoft Power Series track – with over 15 hours of deep dive, hands on content – and the PowerBIUG is giving away one free personalized Power Pack?!

The Power Pack includes a free event pass to 2019 Power Platform Summit Europe, a Power BI t-shirt, User Group stickers, AND 30 minutes one-on-one with the Microsoft Power Series presenter of your choice. Ask questions, get ideas for your latest project, and give feedback to shape the future of Power BI.

To be entered to win please reply to this group post with your latest Power BI challenge, a recent dashboard/reporting win, or a Power BI enhancement idea. The winner will be chosen from the local User Group with the most discussion posts. The local winning PUG will also receive a feature on the PowerBIUG home page, a blog highlighting our plans for 2019, and social buzz.

The Microsoft Power Series will include sessions such as:

• AI in Power BI Service: Data Flow and Machine Learning
• Debugging common DAX and Model performance issues
• Advanced Data Visualization & Storytelling with Power BI Desktop
• And so much more – view the entire lineup here!

Register today with this 10% off UG coupon (Coupon Code: EUPSummit10) and take advantage of advanced registration savings of €200 ending February 28.

Hi Jose, 

Sounds like a brilliant prize. 

I'd like to put my hat into the ring with this recent P.BI challenge and reporting win. 

The client originally wanted to understand volumes for subgroups within the overall data set. The example dashboard I was given had a formula like the following: 

[Calculated_Column] = 

CALCULATE ( 

    SUM([Row_Volume]), 

    FILTER ( 

       'FactTable' , 

        EARLIER([SubGroup_column]) = [SubGroup_column]) 

         ) 

) 

Fairly straight forward. 

The challenge can when the customer asked for the report user to be able to filter the grouping based onparticular columns. 

Now a calculated column will not work as it isn't adaptable to the changed filter context in each visual and according to the front end filter selection. So the solution had to be a measure (unless I missed something obvious). 

However; 

[Measure] =  

CALCULATE ( 

    SUM([Row_Volume]), 

    FILTER ( 

       'FactTable' , 

        EARLIER([SubGroup_column]) = [SubGroup_column]) 

         ) 

) 

returns an error. The outer table reference created by the EARLIER could not find the current row context from which to operate. 

CALCULATE (also SUMX , AvergaeX ect) are row iterators (not sure if that's the correct term). They evaluate the expression per row. So why then could the earlier not find a current row context to operate from? I cant take credit for the answer to this, I found it in a blog post but i cant remember by who.Basically the calculate execution sequence executes the filter section first then returns that whole result for the sum expression to iterate over.  

So in order to align the execution of the calculate row by row iteration and the earlier function the formula must look like this: 

[Measure] = 

CALCULATE ( 

    SUM ( [Row_Volume] ), 

    FILTER ( 

        ALL ( 'FactTable' ), 

        SUMX ( 

            FILTER ( 'FactTable', EARLIER ( [SubGroup_column] ) = [SubGroup_column] ), 

            'FactTabl'[Row_Volume] 

        ) 

    ) 

)) 

What I did discover was the next step. So the customer wanted to be able to filter these volumes by particular columns but still wanted to keep the default grouping from the above measure. My first attempt was with the following measure: 

[Measure] = 

CALCULATE ( 

    SUM ( [Row_Volume] ), 

    FILTER ( 

        ALLEXCEPT ( 'FactTable', [Filter_Column]  ), 

        SUMX ( 

            FILTER ( 'FactTable', EARLIER ( [SubGroup_column] ) = [SubGroup_column] ), 

            'FactTabl'[Row_Volume] 

        ) 

    ) 

)) 

But unfortunately the ALLEXCEPT function does not work as I expected. When [Filter_Column] was filtered using a slicer in the front end, the results within the same sub group would be different. Let me explain with an example. Assume a fact table like this: 

The result of the above measure with no filtering applied would look something like this when displayed in a table visual: 

But when a slicer is used to filter [Filter_Column] to the 1st the results look like this (while subgroup_column is also filtered to a): 

Even though ALLEXCEPT is meant to preserve the measure from being filtered by [GranularDetail_column1] , the fact that it is located on the same table as [Filter_column] means that it affects the calculate iteration and the EARLIER outer table reference. This only arises as a issue when [SubGroup_Column] is exposed to filtering as well. So in row 3 we see a 0 instead of a 2. 

AND FINALLY, THE SOLUTION: 

This unintended behavior of ALLEXCEPT works on the basis that the two columns are in the same table (it tuck a long time to figure that out :)) in order to get around this I created a new table with unique values from [Filter_column] and related it as one to many to the 'FactTable'. The critical piece is that the relationship cross filter direction is set to single. So the final formula looks like this: 

CALCULATE ( 

    SUM ( [Row_Volume] ), 

    FILTER ( 

        ALLEXCEPT ( 'FactTable', 'Filtertable'[FilterColumn]  ), 

        SUMX ( 

            FILTER ( 'FactTable', EARLIER ( [SubGroup_column] ) = [SubGroup_column] ), 

            'FactTabl'[Row_Volume] 

        ) 

    ) 

)) 

When i first posted this yesterday i then tested the sample data only to discover to my horror that the ALLEXCEPT function was working exactly as it should. It seemed there was no issue. But i could still see it in my real data. After a bit of testing I discovered that the issue is more nuanced that I originally thought. I have added in the further details in RED above.

Many thanks to anyone who's taken the time to read this far, this problem was both at the same time; the best fun I've had in work for a long time as well as a nightmare that stopped me from sleeping. Feels good to share. :)