Power BI Exchange

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

Compare Two Measures and Display Reason for Difference

  • 1.  Compare Two Measures and Display Reason for Difference

    Bronze Contributor
    Posted Mar 01, 2021 12:19 PM
    Hi

    I need to compare two measures and display some text in a visual (table) to highlight any differences. I know I probably need to use an IF/SWITCH statement but I'm guessing that I need to use a measure as I want the current filter to be taken into account.

    These are the tests and sample resulting output:

    If measure A is blank and measure B is not blank then output "Measure B Only"
    If measure B is blank and measure A is not blank then output "Measure A Only"
    If measure A <> measure B then output "Different Values"
    Else "Same Values"

    Any pointers or sample DAX would be greatly appreciated.

    Thanks

    ------------------------------
    Rob Littler
    BI Developer
    Warwick
    ------------------------------


  • 2.  RE: Compare Two Measures and Display Reason for Difference

    Gold Contributor
    Posted Mar 01, 2021 01:18 PM
    This worked at a simple level.

    Compare = IF([MeasureA] = [MeasureB],"Same Values",IF(AND(ISBLANK([MeasureA]),NOT(ISBLANK([MeasureB]))),[MeasureB],IF(AND(ISBLANK([MeasureB]),NOT(ISBLANK([MeasureA]))),[MeasureA],"Different Values")))

    Best wishes,

    Ashley


    ------------------------------
    AHsia

    Did my reply help? Please recommend.
    ------------------------------



  • 3.  RE: Compare Two Measures and Display Reason for Difference

    Bronze Contributor
    Posted Mar 03, 2021 03:11 PM
    Hi Ashley, thank you for responding. I've implemented it and it appears to work but the refresh of the table takes ages and times out. Any suggestions on how I could improve the performance?

    ------------------------------
    Rob Littler
    BI Developer
    Warwick
    ------------------------------



  • 4.  RE: Compare Two Measures and Display Reason for Difference

    Gold Contributor
    Posted Mar 03, 2021 04:53 PM
    Hi Rob,

    A lot of times slowness happens when you're crunching a lot of data.  If you want to save processing time you could considering doing the calculations in M.  There is usually a trade-off though, it may take up more processing time on your data refresh.  Below is an example using average as a summary result in a table and then making a comparison.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Ashley Hsia\Desktop\test\Test\test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    GroupT = Table.Group(#"Promoted Headers",{"ID"},{{"A",each List.Average([A])},{"B",each List.Average([B])}}),
    #"Added Custom" = Table.AddColumn(GroupT, "Compare", each if [A] = [B] then "Same Values" else if [A] = null and [B]<>null then [B] else if [A]<>null and [B] = null then [A] else if [A]<>[B] then "Different Values" else null)
    in
    #"Added Custom"

    If you don't want to actually alter your current table, you can reference your current table and then make the calculations.  That will load it as a separate table.

    Hope it works out.

    Best wishes,

    Ashley

    ------------------------------
    AHsia

    Did my reply help? Please recommend.
    ------------------------------



  • 5.  RE: Compare Two Measures and Display Reason for Difference

    Bronze Contributor
    Posted 20 days ago
    Hi Ashley

    Apologies for the late reply. I ended up implementing something along the lines of the following i.e. the testing for blanks in both MeasureA and MeasureB greatly improved the performance.

    VAR _A = [MeasureA]

    VAR _B = [MeasureB]

    VAR _Variance = [MeasureC]

     

    RETURN

     SWITCH(TRUE(),

        AND(ISBLANK(_A), ISBLANK(_B)), BLANK(),

         _Variance = 0,

        "Values Match",

     

        AND (

            ISBLANK ( _A ),

            NOT ( ISBLANK ( _B ) )

        ),

        "MeasureB Only",

     

        AND (

            ISBLANK ( _B ),

            NOT ( ISBLANK ( _A ) )

        ),

        "MeasureA Only",

     

        "Values Differ"

    )



    ------------------------------
    Rob Littler
    BI Developer
    Warwick
    ------------------------------