Custom visual

[lukaspowerbi]

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

[v-shex-msft · Accepted 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

[lukaspowerbi]

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.

[v-shex-msft]

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

[lukaspowerbi]

 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.

[v-shex-msft]

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

[lukaspowerbi]

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")

[v-shex-msft]

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