Forum Discussion
Switch function not working with a slicer
I have a Customer Sales Table with a sum (netSum = SUM(...)) and compare it with a sum of previous time period:
Hi MichaelNiederer ,
The problem is that you have created a column on your model instead of a measure you create the following measure:
Prev_Selection_SwitchCG _1 = SWITCH(TRUE(), [NetSumPrev] >= MIN(CategoryDelNotes[Min]) && [NetSumPrev] < MAX(CategoryDelNotes[MAXREVISED]), 1)Now you should be able to get the result you need.
Be aware that since your max value is blank I created a new column replacing blank by 9999999999
Now if you use this measure on your visual you are able to get the expected result:
If you just filter out the blanks you get the following result the bottom table is the one with the filter applied:
Please see file attached.
7 Replies
Hi MichaelNiederer ,
For this you need to have a table with the buckets and the switch statement, however you do not refer how that part of your model is.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.Hi,
I have solved a similar question in the attached PBI files.
Hope this helps.
Hi MichaelNiederer,
Have you had a chance to review the solution we shared by MFelix Ashish_Mathur? If the issue persists, feel free to reply so we can help further.
Thank you.
Hi, sorry for the delay, i needed some time to prepare example data manually.
So i have some sales table (in original with DateTime column - so i could also use the PreviousDate Filter as a slider it seems - but seems similar with only Date at least) and a report where i display unique elements "DealerId + CustomerNr" (along with a summary of product group sums).
My goal is to limit/filter the report to Combinations where Previous Date SalesSum is within some range - so i can filter important customers which i loose completely e.g. I tried with a switch statement to NetSum Previous Year like suggested but this does not filter anything.
Is this not working properly, because mixing with product groups in the report? I think i am missing something.
example report with demo data:
Thank you for your help.
Kind regards
Hi MichaelNiederer,
To make the slicer work you need to have the field from the disconnected table in the visual as well as the slicer.
Check below Pbix file for reference:
Thank you.Hi MichaelNiederer ,
The problem is that you have created a column on your model instead of a measure you create the following measure:
Prev_Selection_SwitchCG _1 = SWITCH(TRUE(), [NetSumPrev] >= MIN(CategoryDelNotes[Min]) && [NetSumPrev] < MAX(CategoryDelNotes[MAXREVISED]), 1)Now you should be able to get the result you need.
Be aware that since your max value is blank I created a new column replacing blank by 9999999999
Now if you use this measure on your visual you are able to get the expected result:
If you just filter out the blanks you get the following result the bottom table is the one with the filter applied:
Please see file attached.
