Forum Discussion

lukaspowerbi's avatar
lukaspowerbi
Icon for Helper II rankHelper 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,

 

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

    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.

    • 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
        Icon for Helper II rankHelper 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.