cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sandeep_PBI
Frequent Visitor

Compare Columns based on a user selection

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

 

f.PNG

 

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).

 

r.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Compare Columns based on a user selection

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.

View solution in original post

2 REPLIES 2
rajulshah Solution Sage
Solution Sage

Re: Compare Columns based on a user selection

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.

Microsoft
Microsoft

Re: Compare Columns based on a user selection

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.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors