Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello.
I have a table type visual where I want to have my result sets displayed by using two different slicers.
As of now, when I click on one slicer(Region), it displays the result in a table type visual just like it should be, however, when I click on the second one (District/National Council), the result is being replaced.
My goal is to keep both results in my table type visual, basically stack it up. First slicer result + second slicer result
Solved! Go to Solution.
HI @lukaspowerbi,
You can refer to below steps to achieve your requirement.
Sample:
1. Use slicer source columns to create new table.(not need to build relationship with original table)
FirstName = VALUES(DimEmployee[FirstName]) LastName = VALUES(DimEmployee[LastName])
2. Add measure to source table of table visual to check the select status from slicers.
Check Tag = var checkA=IF(COUNTROWS(ALL(FirstName))=COUNTROWS(ALLSELECTED(FirstName)),FALSE(),//block allselect CONTAINS(ALLSELECTED(FirstName[FirstName]),FirstName[FirstName],MAX(DimEmployee[FirstName]))) var checkB=IF(COUNTROWS(ALL(LastName))=COUNTROWS(ALLSELECTED(LastName)),FALSE(),//block allselect CONTAINS(ALLSELECTED(LastName[LastName]),LastName[LastName],MAX(DimEmployee[LastName]))) return if(checkA||checkB,"Y","N")
3. Add two slicer with new table columns as the source, drag measure into table visual and modify the visual level filter to 'is Y".
Result:
Notice: if you not want to block all select, you can remove that part of formula.
Regards,
Xiaoxin Sheng
HI @lukaspowerbi,
You can refer to below steps to achieve your requirement.
Sample:
1. Use slicer source columns to create new table.(not need to build relationship with original table)
FirstName = VALUES(DimEmployee[FirstName]) LastName = VALUES(DimEmployee[LastName])
2. Add measure to source table of table visual to check the select status from slicers.
Check Tag = var checkA=IF(COUNTROWS(ALL(FirstName))=COUNTROWS(ALLSELECTED(FirstName)),FALSE(),//block allselect CONTAINS(ALLSELECTED(FirstName[FirstName]),FirstName[FirstName],MAX(DimEmployee[FirstName]))) var checkB=IF(COUNTROWS(ALL(LastName))=COUNTROWS(ALLSELECTED(LastName)),FALSE(),//block allselect CONTAINS(ALLSELECTED(LastName[LastName]),LastName[LastName],MAX(DimEmployee[LastName]))) return if(checkA||checkB,"Y","N")
3. Add two slicer with new table columns as the source, drag measure into table visual and modify the visual level filter to 'is Y".
Result:
Notice: if you not want to block all select, you can remove that part of formula.
Regards,
Xiaoxin Sheng
Thank you for you reponse. Could you elaborate a little on those three steps? Especially the first step of creating new table by using slicer source columns?
Thank you.
Hi @lukaspowerbi,
>>Could you elaborate a little on those three steps? Especially the first step of creating new table by using slicer source columns?
I share the pbix file as attachments, hope this will be help.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Your file helped tremendously. I got it up and running.
One follow up question:
if I wanted to include, one more filter to have same functionality just like the previous two, how would I tweek the formula?
I have the unfinished formula below and from the return statement I am not sure what follows:
Check Tag =
var checkA=IF(COUNTROWS(ALL(Title))=COUNTROWS(ALLSELECTED(Title)),FALSE(),//block allselect
CONTAINS(ALLSELECTED(Title[TITLE]),Title[TITLE],MAX('All Events'[TITLE])))
var checkB=IF(COUNTROWS(ALL('District Council/NAtional Council'))=COUNTROWS(ALLSELECTED('District Council/NAtional Council')),FALSE(),//block allselect
CONTAINS(ALLSELECTED('District Council/NAtional Council'[District / National Council]),'District Council/NAtional Council'[District / National Council],MAX('All Events'[District / National Council]))) var checkC=IF(COUNTROWS(ALL(Region))=COUNTROWS(ALLSELECTED('Region')),FALSE(),//block allselect
CONTAINS(ALLSELECTED(Region[Region]),'Region'[Region],MAX('All Events'[Region])))
return
if(checkA||checkB||check C||,"Y","N")
Thank you very much.
Hi @lukaspowerbi,
If the new condition is 'or' logic to current condition, you can direct use '||' with new condition:
Check Tag = var checkA=xxxxx var checkB=xxxxx var checkC=xxxxx return if(checkA||checkB||checkC,"Y","N")
If new relationship is like "A and C" or 'B and C', you can use bracket to split current condition and the new one.
Check Tag = var checkA=xxxxx var checkB=xxxxx var checkC=xxxxx return if((checkA||checkB)&&checkC,"Y","N")
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
Thank you for your help very much. I got it running.
I have one more question. I am actually adding one more filter to my logic that is like: "A and D" or 'B and D' or "C and D. In other words, once I click on filter D (in my case Fiscal Year) I want to be able then filter by using additional filters that would use result set that I got from filter D.
Below is the formula I am using. It looks like it runs okay but when I hit enter, the table visual can't display with the following error message in the attachment.
Check Tag = var checkA=IF(COUNTROWS(ALL(Title))=COUNTROWS(ALLSELECTED(Title)),FALSE(),//block allselect CONTAINS(ALLSELECTED(Title[TITLE]),Title[TITLE],MAX('All Events'[TITLE]))) var checkB=IF(COUNTROWS(ALL(Region))=COUNTROWS(ALLSELECTED(Region)),FALSE(),//block allselect CONTAINS(ALLSELECTED('Region'[Region]),'Region'[Region],MAX('All Events'[Region])))
var checkC=IF(COUNTROWS(ALL('District / National Council'))=COUNTROWS(ALLSELECTED('District / National Council')),FALSE(),//block allselect CONTAINS(ALLSELECTED('District / National Council'[District / National Council]),'District / National Council'[District / National Council],MAX('All Events'[District / National Council])))
var checkD=IF(COUNTROWS(ALL('Fiscal Year'))=COUNTROWS(ALLSELECTED('Fiscal Year')),FALSE(),//block allselect CONTAINS(ALLSELECTED('Fiscal Year'[Fiscal Year]),'Fiscal Year'[Fiscal Year],MAX('All Events'[Fiscal Year]))) return
if((checkA||checkB||checkC) & checkD, "Y","N")
Hi @lukaspowerbi,
if((checkA||checkB||checkC) & checkD, "Y","N")
It seems like you have missed one '&' character, you can add it to your formula.
if((checkA||checkB||checkC) && checkD, "Y","N")
BTW, single '&' is used to merge text, it similar as CONCATENATE function: "ABC"&"BCD"= CONCATENATE ("ABC","BCD") ="ABCBCD"
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
50 | |
18 | |
17 | |
16 | |
8 |