Hi,
I am trying to create a filter using DAX with the values that are calculated values from two different columns.
I have multiple accounts that has local sales, national sales, international sales columns. The other two columns are LocalSales-National Sales, NationalSales- International Sales.
I am trying to create a filter/Slicer that have below combinations.
LocalSales<National Sales
2.NationalSales<International Sales
3.NationalSales>International Sales
4.No difference.
So, when I select 1. LocalSales<National Sales, the output should be
I have written an If condition and tried with switch as well, it works fine when only one condition satisfies, but when there is a scenario where two conditions satisfy, since the first condition is satisfied, it doesnt even evaluate the second condition and those records are not being displayed when that particular option is selected like in the case of account A400.
Is there anyway that this can be implemented?
Thank you all for the support! Comparing Sales.xlsx (11.6 KB)
good morning @Vsb79 , as you did not provide a PBIX file (which is very useful even in cases like this) - I have made a few assumptions about your report:
you followed best practices and are using measures for all of the values used in your report (not just pulling in the column)
the two variance columns (Local Sales - National Sales, National Sales - International Sales) were created with measures and are not loaded into your data model.
You are using a disconnected table for your filter statement.
Now, with all of these in place, here is my recommended solution:
I have created measures for each of the values in the report, and hidden the original columns so they don’t get used accidentally.
I have created a “Selected Status” measure to give me the value selected by the slicer (if nothing is selected, then “No difference” is the default)
Selected Status =
SELECTEDVALUE( 'Account Status'[Status], "No difference" )
I have created a Status Toggle measure to used to determine which values will show on your final report table:
Status Toggle =
VAR _LocalNational = [Local Sls - National Sls]
VAR _NationalInternational = [National Sales - International Sales]
VAR _NoDifference = _LocalNational + _NationalInternational
RETURN
SWITCH( TRUE(),
[Selected Status] = "LocalSales<NationalSales" && _LocalNational < 0, 1,
[Selected Status] = "NationalSales<InternationalSales" && _NationalInternational < 0, 1,
[Selected Status] = "NationalSales>InternationalSales" && _NationalInternational > 0, 1,
[Selected Status] = "No difference" && _NoDifference = 0, 1,
0 )
And finally - I have added that to a table with the toggle switch used as a filter on the visual (but not showing on the table)
Thank you @Heather Heather, this is really good. Yes, all my values and variance columns are measures in my report. I implemented it and its working as expected. But, the select all option does not work as expected. Is there a way where we can have a select all option as well where it gives all the records that has
LocalSales <(less than) NationalSales
LocalSales > NationalSales
NationalSales>InternationalSales
NationalSales<InternationalSales and
_NoDifference=0 ?
When I created one more option to the switch statement,
It does show all the records but the other options stopped working.
Is my DAX wrong.
If the user also wants “Select All” Option(That has all combinations) along with the other above mentioned options, how can the solution be implemented. eDNA Solution - Disconnected Table for Slicer.pbix (24.0 KB)
Remember, the SWITCH statement returns either 1 or 0 for each line of code. And the filter on the visual is to return the line if the result of the SWITCH for that line is 1.
So, in it’s simplest form, your “Select all” line should look like this:
[Selected Status] = “Select all”, 1,
Basically, if the Selected Status is “Select all”, return a 1 on every line - no additional condition.
if you are worried that you might have an account that does not have a value for Local, National or international sales - and you don’t want to show that line, you could make the select all line look like this:
