Forum Discussion
Multiselect with Disconnect Table
Hi all,
I have 2 disconnected tables. I am taking data from one of the tables and a slicer (Patient Copay) from another. Now I need to filter grids based on the slicer. To do that I am creating a measure and putting it as visual level filters on the grids. I have created the measure but the issue that I am facing is that I have to make the slicer multiselect and combination of SWITCH and SELECTEDVALUE is not functioning properly. Below is my measure:
IF(ISFILTERED('Patient Copay Amount'[Patient Copay]),
SWITCH(
TRUE(),
SELECTEDVALUE('Patient Copay Amount'[Patient Copay]) = "$0-$3000" && MAX(Copay Table[Total Copay])<=3000, 1,
SELECTEDVALUE('Patient Copay Amount'[Patient Copay]) = "$3000-$5000" && MAX(Copay Table[Total Copay])>3000 && MAX(Copay Table[Total Copay])<=5000, 1,
SELECTEDVALUE('Patient Copay Amount'[Patient Copay]) = "$5000-$7500" && MAX(Copay Table[Total Copay])>5000 && MAX(Copay Table[Total Copay])<=7500, 1,
SELECTEDVALUE('Patient Copay Amount'[Patient Copay]) = "$7500-$10000" && MAX(Copay Table[Total Copay])>7500 && MAX(Copay Table[Total Copay])<=10000, 1,
SELECTEDVALUE('Patient Copay Amount'[Patient Copay]) = "$10000-$12500" && MAX(Copay Table[Total Copay])>10000 && MAX(Copay Table[Total Copay])<=12500, 1,
SELECTEDVALUE('Patient Copay Amount'[Patient Copay]) = "Above $12500" && MAX(Copay Table[Total Copay])>12500, 1,
0
),1
)
What I am doing now is putting the measure as visual level filter and putting it as 1. But this only works for single select.
Thanks,
Yashwant
Hi yashwant101
Please try the following measure:Measure = IF(ISFILTERED('Patient Copay Amount'[Patient Copay]), SWITCH( TRUE(), "0-50" IN VALUES('Patient Copay Amount'[Patient Copay]) && [Total Copay]<=30, 1, "50-100"IN VALUES('Patient Copay Amount'[Patient Copay])&& [Total Copay]>30 && [Total Copay]<=100, 1, "100-150"IN VALUES('Patient Copay Amount'[Patient Copay]) && [Total Copay]>100 && [Total Copay]<=160, 1, "150-200"IN VALUES('Patient Copay Amount'[Patient Copay]) && [Total Copay]>160, 1, 0 ),1 )
Result:Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi yashwant101 ,
The question is that you are using the SELECTEDVALUE this is dependent on a single select value and that create your limitation of the formula. Taking into account that you have a combination of two tables you need to configure a different setup that does the combination between this value and then return a value for you to filter upon.
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 MFelix ,
Thanks for your response. Uploaded the file here
In this file, as you can see we have a grid and a custom time slicer. I have added one column in the end a sPatient wise Total Copay. We need the filter in right (of the copay bucket) to filter the grid, and the copay bucket slicer is multiselect depending on the time slicer.
Your help is highly appreciated.
Regards,
Yashwant
Hi yashwant101
To handle multiple selections, you can use the "IN" function along with "VALUES" to check if the Total Copay falls within any of the selected ranges. Here’s an updated version of your measure:Measure = IF( ISFILTERED('Patient Copay Amount'[Patient Copay]), SWITCH( TRUE(), MAX(Copay Table[Total Copay]) <= 3000 && "$0-$3000" IN VALUES('Patient Copay Amount'[Patient Copay]), 1, MAX(Copay Table[Total Copay]) > 3000 && MAX(Copay Table[Total Copay]) <= 5000 && "$3000-$5000" IN VALUES('Patient Copay Amount'[Patient Copay]), 1, MAX(Copay Table[Total Copay]) > 5000 && MAX(Copay Table[Total Copay]) <= 7500 && "$5000-$7500" IN VALUES('Patient Copay Amount'[Patient Copay]), 1, MAX(Copay Table[Total Copay]) > 7500 && MAX(Copay Table[Total Copay]) <= 10000 && "$7500-$10000" IN VALUES('Patient Copay Amount'[Patient Copay]), 1, MAX(Copay Table[Total Copay]) > 10000 && MAX(Copay Table[Total Copay]) <= 12500 && "$10000-$12500" IN VALUES('Patient Copay Amount'[Patient Copay]), 1, MAX(Copay Table[Total Copay]) > 12500 && "Above $12500" IN VALUES('Patient Copay Amount'[Patient Copay]), 1, 0 ), 1 )Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Thanks for your response. If I use this formula when the first condition becomes correct, the rest are not executed. But since it is multiselect I want all teh statements to be executed. I have placed the file here
I have explained the requirement in the other response. Please have a look and let me know a way to achieve this.
Regards,
Yashwant
Hi yashwant101
Please try the following measure:Measure = IF(ISFILTERED('Patient Copay Amount'[Patient Copay]), SWITCH( TRUE(), "0-50" IN VALUES('Patient Copay Amount'[Patient Copay]) && [Total Copay]<=30, 1, "50-100"IN VALUES('Patient Copay Amount'[Patient Copay])&& [Total Copay]>30 && [Total Copay]<=100, 1, "100-150"IN VALUES('Patient Copay Amount'[Patient Copay]) && [Total Copay]>100 && [Total Copay]<=160, 1, "150-200"IN VALUES('Patient Copay Amount'[Patient Copay]) && [Total Copay]>160, 1, 0 ),1 )
Result:Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
