Dublin Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Subject: Looking for 1:1 time with the Microsoft Product Team?

  • 1.  Subject: Looking for 1:1 time with the Microsoft Product Team?

    Silver Contributor
    Posted Feb 16, 2019 06:26 PM
    Edited by Jose Almeida May 06, 2019 12:24 PM

    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.



    ------------------------------
    Jose Almeida
    Data Analytics & Reporting Consultant
    bordalos.com
    ------------------------------


  • 2.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Gold Contributor
    Posted Feb 20, 2019 03:02 AM
    Hi Jose,

    Thank you for letting us know. Seems a very interesting opportunity.


    Regards,

    ------------------------------
    Tuhin Tapadar
    Data Analyst
    Dublin
    894164048
    ------------------------------



  • 3.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Posted Feb 23, 2019 02:00 PM

    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 


    The Pareto chart is a handy visual, but is not so easy to build in either excel or Power BI, one of the most common issues  you will come across when building your Pareto chart is flat lines.
    This will happen if your data have the same figure when ranking using a measure.
     In a Pareto chart, information is provided about an individual product or category as a bar, and a cumulative scale as a line which comprises all bars.

    If you have tie figures in your data instead of a nice flowy line, you will end up with something like this:

    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 

    ​​​​​​​​

    ------------------------------
    Cecilia Pereira
    @cissagmr
    ------------------------------



  • 4.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Gold Contributor
    Posted Feb 25, 2019 03:27 AM
    Hi @Cecilia Pereira,

    It is really a great post and much helpful for everyone. Although, this group is not a very active one. But I hope some great post like this one can make a lot of differnce and able to make it an active group.

    Thanks,
    Tuhin

    ------------------------------
    Tuhin Tapadar
    Data Analyst
    Dublin
    894164048
    ------------------------------



  • 5.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Silver Contributor
    Posted Feb 25, 2019 06:48 PM
    Hi @Cecilia Pereira

    Thank you for participating and sharing your win with the group.
    A Pareto Chart is a powerful visual that can apport big value for the business. Without doubt that is an ingenious way to solve tied values on Pareto Charts and especially that work correctly with slicers.

    We hope to see you at our next meet up event


    Regards,
    Jose


    ------------------------------
    Jose Almeida
    Data Analytics & Reporting Consultant
    bordalos.com
    ------------------------------



  • 6.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Posted Feb 23, 2019 05:20 PM
    Edited by Gerald O'Flanagain Feb 23, 2019 05:50 PM


  • 7.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Posted Feb 24, 2019 05:18 PM

    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: 

     

     

    Row_id 

    SubGroup_Column 

    GranularDetail_Column1 

    GranularDetail_Column2 

    Filter_column 

    Row_Volume 

    1 

    a 

    x 

    yy 

    2nd 

    1 

    2 

    a 

    y 

    yx 

    1st 

    1 

    3 

    a 

    z 

    zx 

    2nd 

    1 

    4 

    a 

    x 

    xy 

    1st 

    1 

    5 

    a 

    x 

    yz 

    2nd 

    1 

    6 

    b 

    y 

    yy 

    1st 

    1 

    7 

    b 

    y 

    yx 

    2nd 

    1 

    8 

    b 

    z 

    zx 

    1st 

    1 

    9 

    b 

    z 

    xy 

    2nd 

    1 

    10 

    b 

    z 

    yz 

    2nd 

    1 

    11 

    b 

    x 

    yz 

    1st 

    1 

     

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

     

    SubGroup_Column 

    GranularDetail_Column1 

    Measure 

    a 

    x 

    5 

    a 

    y 

    5 

    a 

    z 

    5 

    b 

    x 

    6 

    b 

    y 

    6 

    b 

    z 

    6 

     

    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): 

     

    SubGroup_Column GranularDetail_Column1 Measure
    a x 2
    a y 2
    a z 0


    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 wellSo 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. :)



    ------------------------------
    Gerald O'Flanagain
    Reporting Specialist
    Dublin
    868678
    ------------------------------



  • 8.  RE: Subject: Looking for 1:1 time with the Microsoft Product Team?

    Silver Contributor
    Posted Feb 25, 2019 06:23 PM
    Hi @Gerald O'Flanagain,

    First of all,  thank you for participating. That is a great win.

    The Evaluation Context is no doubt one of the most important and complex DAX concepts.  There are good articles out there that can help to understand the dynamics of "Row Context" and " Filter Context". Of course, the "Italians" have it well covered at SQLBI row context and filter context in dax or Ms Docs  https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics .

    In relation to  EARLIER Alberto Ferrari said "is probably the most hated function in DAX and many people find it hard to use it"  :) More at Variables in DAX - SQLBI

    Your final aproach, creating a "dimetion table" to used as filter would be one of my first aproaches when I read you user story.
    I am always a fan of Star Schemas whenever these are  possible. It turns modeling much easier.

    Well, if you plan to attend our next meet up we can chat a bit more on this.

    Cheers,
    Jose



    ------------------------------
    Jose Almeida
    Data Analytics & Reporting Consultant
    bordalos.com
    ------------------------------