Search code examples
differenceslicerspowerbi-desktop

How to get the difference between the values selected by slicer?

I am new to Power BI and currently I am working with table visulaizations and slicers.

My data is as follows

Student table:

enter image description here

Date table:

enter image description here

Exam table:

enter image description here

The relationships within the table are as follows: enter image description here

I want an output like the image shown below, I would like to create 2 table visuals that can be filtered on Student Name, Classroom and also have slicer on 2 dates. I need to compute minimum score. The user must be able to select 2 dates at a time on the slicer, the first date selected on the slicer should be attached to my 'Min Score at date1' and second date selected on the slicer should be attached to my 'Min Score at date2', and the third column 'Difference in Score' must be able to calculate the difference between the Min Score at date1 and Min Score at date2.

Similarly I also want to calculate the average minimum score too

enter image description here

Please let me know how to proceed or what alternative formula or query or method should I apply to get the desired result.Thanks!

Solution

  • Before I start, let me mention that this example was done in SSAS so it may need some tweaking in PowerBi but the logic is identical nonetheless.

    First create a clone date table and call it something else e.g. 'Compare Date'. Next, create an inactive, one to many relationship between the 'Compare Date' and your 'Fact' table, see the image below, in this case I am joining on [Year Month], you will need to adjust to fit your needs:

    enter image description here

    If you are unsure how to do this, just right click on the new table and select the create relationship option, ensure that the relationship is like the image below:

    enter image description here

    Once this has been done, right click on the 'relationship' and mark it as inactive.

    Now that you have the new date table and the relationships set up, I want you to create a few DAX measures:

    Min Date 1 = Min('Student Table'[Score])

Min Date 2 = CALCULATE(Min('Student Table'[Score]), ALL('Dates'), USERELATIONSHIP('Compare Date'[Date], 'Fact'[Date]))

Avg Date 1 = AVERAGE('Student Table'[Score])

Avg Date 2 = CALCULATE(AVERAGE('Student Table'[Score]), ALL('Dates'), USERELATIONSHIP('Compare Date'[Date], 'Fact'[Date]))

Delta Min = [Min Date 2] - [Min Date 1]

Delta Avg = [Avg Date 2] - [Avg Date 1]

    These measures will calculate exactly what you need and can be filtered independently via two date slicers tied to each date table. The rest is just busy work.

    I hope this helps.