Forum Discussion

BSM1985's avatar
BSM1985
Helper IV
4 years ago

Custom table slicer

Hi Team,

 

I want to filter the existing table based on selection in the slicer which is populated thru custom table.

 

Step 1:  I created a custom table using DAX with below values

Color

Green

Orange

All Colors

 

Step 2: Added the slicer based on the above column and now the users see "Green", "Orange" and "All Colors" in the slicer

 

Step 3: The original(fact) table has a field Color but with 2 values only "Green" and "Orange".

 

Step 4: Now the requirement is:

1. When users select "Green" (Which is based on the custom table created using DAX in Step 1) then the fact table should be filtering on the color "Green"

 

2. When users select "Orange" (Which is based on the custom table created using DAX in Step 1) then the fact table should be filtering on the color "Orange"

 

3.When users select "All Colors" (Which is based on the custom table created using DAX in Step 1) the the fact table should NOT be filtering on the color and it should display all the colors.

 

The condition will be something like this in general and then I will use the below calculated field in filter and choose "True"

IF ( [CustomTable].[Color]="Green" then [FactTable].[Color]="Green"

ELSEIF [CustomTable].[Color]="Orange" then [FactTable].[Color]="Orange"

ELSEIF [CustomTable].[Color]="All Countries" then 1=1

END)

 

Please let me know how to achieve this.

 

Thanks,

BSM

Need Help

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    This is certainly possible to do as you describe but unless there's a strong reason for doing otherwise, I'd recommend taking advantage of the Select all option for the slicer.

     

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi BSM1985 ,

     

    Does the custom table have relationship with fact table?

    if no , try this measure 

    m =
VAR _s =
    SELECTEDVALUE( 'custome table'[color] )
RETURN
    IF( _s = "ALL Colors", 1, IF( 'fact table'[color] = _s, 1, BLANK() ) )

     

    then put the measure in the filters on the visual with you want to apply and set item is 1

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.