Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to get difference between two columns in Matrix based on slicer selection of Year in Power Bi

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Here you go https://www.dropbox.com/t/sMyzwonHckNHvdKX
    Original Measure code remains the same

    Measure = COUNTROWS ( DISTINCT ('Table'[Value] ) )

    New Measure code is

    Measure1 = 
VAR MaxYear = MAX ( 'Table'[Year] )
VAR MinYear = MIN ( 'Table'[Year] )
VAR MaxYearValue =
    CALCULATE ( 
        [Measure],
        'Table'[Year] = MaxYear
    )
VAR MinYearValue =
    CALCULATE ( 
        [Measure],
        'Table'[Year] = MinYear
    )
VAR Difference = MaxYearValue - MinYearValue
VAR Result =
    SWITCH (
        TRUE,
        NOT HASONEVALUE ( 'Table'[Year] ), Difference,
        COUNTROWS ( ALLSELECTED ('Table'[Year] ) ) = 1, BLANK(),
        [Measure]
    )
RETURN
    Result

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks For Your Solution .
One thing is the values column in your Pbix is numbers like 10 , 20  26 ,but  my requirement is,  values  column is codes like b010 , c350, d6f , ng6 . I categorized them as Distinct count . So they look like numbers in my attached file . I think the DAX is almost correct .  Can you modify dax by using my scenario .
Thanks For such great Help .

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

You can just refer your measure name instead of [Measure] and it should work. Otherwise, you can send your file with a sample data and I'll do it for you. 
If you are satisfied please mark my reply as accepted solution. Kudoes are also appreciated. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago


     I replaced values with codes . My requirement is look like this . I categorized them as distinct count ( from column tools ) .What I want is exactly like your output  . Measure 1 is exactly corect . I think we need to modify the Measure , with codes (Distinct Count ) .Please modify the dax .,It would work fine. Am unable tho send the file .
    Kudos !
    Thanks for such quick response 

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi Anonymous 
      Here you go https://www.dropbox.com/t/sMyzwonHckNHvdKX
      Original Measure code remains the same

      Measure = COUNTROWS ( DISTINCT ('Table'[Value] ) )

      New Measure code is

      Measure1 = 
VAR MaxYear = MAX ( 'Table'[Year] )
VAR MinYear = MIN ( 'Table'[Year] )
VAR MaxYearValue =
    CALCULATE ( 
        [Measure],
        'Table'[Year] = MaxYear
    )
VAR MinYearValue =
    CALCULATE ( 
        [Measure],
        'Table'[Year] = MinYear
    )
VAR Difference = MaxYearValue - MinYearValue
VAR Result =
    SWITCH (
        TRUE,
        NOT HASONEVALUE ( 'Table'[Year] ), Difference,
        COUNTROWS ( ALLSELECTED ('Table'[Year] ) ) = 1, BLANK(),
        [Measure]
    )
RETURN
    Result

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank You So Much Tamerj1 .
        You deserve to be  a super user .
        Kudos ! Kudos !