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 response Ibbarau ,
 My values are actually codes ( like m124 , b016, cd56 ) . I categorized them as Distinct count . so when I use  SUM  the DAX is not working.
If you need  any info. Please ask .
Thanks

 

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
4 years ago

Ok. If the numbers in the fields are distinct count of categories you can replace the SUM of the measure I have sent with DISTINCTCOUNT function in DAX.

I hope that work

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your solution 
    I also wrote one dax it worked out

    Measure =
    VAR X = CALCULATE(DISTINCTCOUNT(table[ Code]),FILTER(table,table[Year]=MIN(table[Year])))
    var y =CALCULATE(DISTINCTCOUNT(table[Code]),FILTER(table , table[Year]=MAX(table[Year])))
    Return
    y-X
    but the same dax is not working for calculated column. Why . Can you explain
     can you write suitable Dax for calculated column .

    Thank you so much .