Compare Columns based on a user selection

[Sandeep_PBI]

I have a table with columns (Student ID, Category, 2017,2018,2019,2020). following is the example 

I have a slicer (dropdown) on the report which shows distinct years (2018,2019,2020).

The task I am trying to complete is to show a report with a table as following (example assuming the user selected 2018 in the slicer and the results based on the sample data above should be as following) that shows if the Student changed the selection compared to the previous year(2017 in the following case) based on the year selected from the slicer (2018 for the).

I have tried several ways but was not able to get the desired output. how can this be achieved using DAX?

[v-easonf-msft · Accepted solution]

Hi , @Sandeep_PBI 

Try measure as below:

change =
VAR _id =
    SELECTEDVALUE ( Category[Student ID] )
VAR _category =
    SELECTEDVALUE ( Category[Category] )
VAR _year =
    SELECTEDVALUE ( Category[Year] )
RETURN
    IF (
        CALCULATE (
            CONCATENATEX ( Category, Category[Value] ),
            FILTER (
                ALL ( Category ),
                Category[Student ID] = _id
                    && Category[Category] = _category
                    && Category[Year] = _year - 1
            )
        )
            = SELECTEDVALUE ( Category[Value] ),
        0,
        1
    )

Here is a demo.

Pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajulshah]

Hello @Sandeep_PBI,

You need to unpivot the columns and then use the measure.

Please see below my M Query below:

let
    Source = *Your Source Here*,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Category", type text}, {"2017", type text}, {"2018", type text}, {"2019", type text}, {"2020", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Student ID", "Category"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}})
in
    #"Renamed Columns"

And then you can use the following measure:

Change = 
VAR SelectedYear = IF(NOT(ISFILTERED(Category[Year])),MAX(Category[Year]),SELECTEDVALUE(Category[Year]))
VAR ValueSelectedYear = MINX(FILTER(Category,Category[Year]=2020),Category[Value])
VAR ValuePreviousYear = CALCULATE(MIN(Category[Value]),ALL(Category[Year]),Category[Year]=SelectedYear-1)
RETURN IF(ValuePreviousYear=ValueSelectedYear,1,0)

Hope this helps.