cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lukaspowerbi Regular Visitor
Regular Visitor

Custom visual

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 resultEvent.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Custom visual

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

1.PNG

 

Result:
2.PNG3.PNG

 

Notice: if you not want to block all select, you can remove that part of formula.

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
7 REPLIES 7
Community Support Team
Community Support Team

Re: Custom visual

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

1.PNG

 

Result:
2.PNG3.PNG

 

Notice: if you not want to block all select, you can remove that part of formula.

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
lukaspowerbi Regular Visitor
Regular Visitor

Re: Custom visual

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.

Community Support Team
Community Support Team

Re: Custom visual

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
lukaspowerbi Regular Visitor
Regular Visitor

Re: Custom visual

 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.

Community Support Team
Community Support Team

Re: Custom visual

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
lukaspowerbi Regular Visitor
Regular Visitor

Re: Custom visual

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. all events.JPG

 

 

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")
Community Support Team
Community Support Team

Re: Custom visual

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |