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

     

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 .