Custom table slicer

[BSM1985]

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

[v-chenwuz-msft]

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.

[AlexisOlson]

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.