Forum Discussion

lukaspowerbi's avatar
lukaspowerbi
Helper II
9 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
lukaspowerbi
Helper II
9 years ago

 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      lukaspowerbi
      Helper II
      9 years ago

      Hi Anonymous , 

       

      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")
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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