DAX – Calculating the difference between 2 selections on a Slicer
Once again, I really enjoy answering questions on the Power BI Community, it keeps me challenged on how to find solutions to people’s requirements.
This time the challenge was that a user on the forum was looking to calculate the difference between 2 selections made on a slicer. In this example the slicer was on a Product.
Below is the example that I was sent
I was certain that I could get this done by using a DAX measure.
I was able to get this done by using the following measure below. Not only that I created it in a way that I could select 2 products from a single slicer which would then give me the result I was looking for.
Based on the above example the result I was looking for is 63.
Below is the selection that I had on the single Product Slicer
Here is the DAX measure with an explanation below.
Selection = VAR Top1N = VALUE ( CALCULATE ( CONCATENATEX ( TOPN ( 1, VALUES ( 'dProduct'[Id_Product] ) ), 'dProduct'[Id_Product], ", " ) ) ) VAR Top2N = VALUE ( CALCULATE ( CONCATENATEX ( TOPN ( 1, VALUES ( 'dProduct'[Id_Product] ), 'dProduct'[Id_Product], DESC ), 'dProduct'[Id_Product], ", " ) ) ) VAR FirstSelectedValue = CALCULATE ( SUM ( 'fSales'[Quantity] ), 'dProduct'[Id_Product] = Top1N ) VAR SecondSelectedValue = CALCULATE ( SUM ( 'fSales'[Quantity] ), 'dProduct'[Id_Product] = Top2N ) RETURN SecondSelectedValue  FirstSelectedValue

Lines 2 – 11
 The first part is to convert the result into a VALUE so that it can then be used later in the filter of the measure.
 Here I am finding the first or Top1N value from the concatenated list
 There is already a relationship between the fact table and the product table on the column ID_Product
 The result for Product 2 is the ID_Product as shown below.

Lines 12 – 21
 The first part is to convert the result into a VALUE so that it can then be used later in the filter of the measure.
 Here I am finding the last value from the contatenated list.
 The result for Product 4 is the ID_Product as shown below.

Lines 22 – 23
 Now I am taking the result from my Top1N and using this to filter my measure to return the quantity where ID_Product = 2
 As shown below is the result I am expecting

Lines 24 – 25
 Now I am taking the result from my Top2N and using this to filter my measure to return the quantity where ID_Product = 4
 As shown below is the result I am expecting

Lines 26 – 27
 I am then finally subtracting the two values to get the result I am looking for.
UPDATE: 10 May 2019 – I got the following update from Chris, how has modified the code in a different way. What I really like is that he has put in a condition to see if more than 2 products are selected and if so then return an error. Thanks to Chris
Selection = VAR SelectedProducts = VALUES ( dProduct[Id_Product] ) VAR MinValue = MINX ( SelectedProducts, CALCULATE ( SUM ( fSales[Quantity] ) ) ) VAR MaxValue = MAXX ( SelectedProducts, CALCULATE ( SUM ( fSales[Quantity] ) ) ) VAR Difference = MaxValue  MinValue VAR NumProductsSelected = COUNTROWS ( SelectedProducts ) VAR Result = IF ( NumProductsSelected = 2, Difference, ERROR ( "Please select only 2 products." ) ) RETURN Result
I really do love using the variables in a DAX measure, not only does it allow me to break up the measure into bite size chunks, it also allows me to debug the values to ensure that I am getting the expected results as I am working through the measure.
If there are any comments or suggestions, please let me know.
Here is the link to the associated PBIX: Calculating the difference between 2 selections on a Slicer.pbix
Interesting solution, but how to avoid selecting more then 2 products. I’m 100% sure it will take just few seconds for users to do it.
Hi there
Thanks for the comment, and yes that could certainly be done, I would like to think that explaining to them to only select 2 values to compare would work. If they selected more then the output would be incorrect?
[…] May 8, 2019 […]
Chris code is not working, i have same task to be completed. But could not able to succeeding in executing this code chris.
Hi there
What is the error you are getting?
And have you debugged your code when using the variables to see where it is returning the wrong data?
Here is a blog post from SQLBI.COM on how to debug with variables: https://guyinacube.com/2018/05/23/debugdaxusingvariablespowerbi/
I think I came up with a slightly cleaner way to do it:
Selection =
VAR SelectedProducts =
VALUES(dProduct[Id_Product])
VAR MinValue =
MINX(
SelectedProducts
,CALCULATE( SUM(fSales[Quantity]) )
)
VAR MaxValue =
MAXX(
SelectedProducts
,CALCULATE( SUM(fSales[Quantity]) )
)
VAR Difference =
MaxValue – MinValue
VAR NumProductsSelected =
COUNTROWS(SelectedProducts)
VAR Result =
IF(
NumProductsSelected = 2
,Difference
,ERROR(“Please select only 2 products.”)
)
RETURN
Result
1) Build a table of selected products with VALUES()
2) Iterate over that table twice to find the min and max values using MINX() and MAXX()
3) Calculate the difference between max and min
4) Check that the only 2 products have been selected before returning the value.
I loved the thought exercise, thanks for a fun challenge!
~ Chris
Hi Chris
That is really cool, I will update my blog post with this code and it is good to throw the error if more than 2 products are selected.
Hi Gilbert,
I added the error message into the code allowing the user to understand the error. I placed result in a multi row card and this worked.
Selection2 =
VAR errorMessage = “Select 2 items to obtain result”
VAR SelectedProducts =
VALUES ( dProduct[Id_Product] )
VAR MinValue =
MINX ( SelectedProducts, CALCULATE ( SUM ( fSales[Quantity] ) ) )
VAR MaxValue =
MAXX ( SelectedProducts, CALCULATE ( SUM ( fSales[Quantity] ) ) )
VAR Difference = MaxValue – MinValue
VAR NumProductsSelected =
COUNTROWS ( SelectedProducts )
VAR Result =
IF (
NumProductsSelected = 2,
Difference, errorMessage
)
RETURN
Result
Hi Maria, thanks for adding in the code, that is a wonderful addition.
Hey guys!
Amazing job, this could help me a lot. But I want to select months as dimension rather than product in your example.
So when I select Feb. 2020 and Nov. 2019, I want to see the difference between the two.
I have almost made it work with your formula – the problem is that I always see the difference between the two, regardless of which period comes first or second.
So if I select February 2020 and November 2019, the result should be negative (Decrease) but instead I get the correct number but POSITIVE.
Here are links to my screenshots of the DAX and the error on my graph. How do you think I can change it? Maybe use some rank forumula within the variables?
Link to screenshot: https://drive.google.com/drive/folders/1FbNNMIYHnagfuaLxRez3cEXBMZC8NRrR?usp=sharing
Here is my DAX, based on Chris’ shorter version and my own tables:
FIN_Periodic_TimeDifference =
VAR SelectedTime =
VALUES(Time_Lookup[Period])
VAR MinValue =
MINX(SelectedTime;CALCULATE([FIN_Periodic]))
VAR MaxValue =
MAXX(SelectedTime;CALCULATE([FIN_Periodic]))
VAR Difference = MaxValue – MinValue
VAR NumPeriodsSelected =
COUNTROWS(SelectedTime)
VAR Result =
IF (
NumPeriodsSelected = 2;
Difference;
ERROR(“Please select only two periods”)
)
RETURN
Result
Hi there,
I would debug your DAX to make sure what is being returned from the MinValue and MaxValue
Once you know what those are you can then build in some logic that if the MinValue is later than the MaxValue make it Negative, otherwise Positive?
Great idea. I changed the Return to check this.
Max Value is always returning the maximum value, regardless of selected time period.
So the issue I have is that I want Max Value to represent most recent time period selected somehow, and opposite for Min Value.
Could I rewrite the DAX for this purpose somehow? I am terrible at this, so any guidance would be greatly appreciated.