Forum Discussion

Sandeep_PBI's avatar
Sandeep_PBI
Frequent Visitor
6 years ago
Solved

Compare Columns based on a user selection

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?

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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.

2 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    6 years ago

    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.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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.