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.

Highlighted
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: | |

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 148 members 1,594 guests
Please welcome our newest community members: