Slicer to filter only a particluar column specified by user

[smpa01]

Hi,

I have the following table

When I choose a particular city from the slicer for example,"A", I want only the CAT1 value to be filtered and CAT2 value to remain unchanged. The effect of my desired filtering would be following.

Is it possible to achieve ? The raw data is following

City	CAT1	CAT2
A	100	-100
B	200	-200
C	300	-300
D	400	-400
E	500	-500

Thank you in advance.

[LivioLanzo · Accepted solution]

Hi @smpa01

You are going to have to unpivot your table like this (Power Query is your friend):

Then, build the below model, the Cities Filter table will be the one used in the slicer:

Thent his measure should do what you want:

Measure = 
IF(
    SELECTEDVALUE( Categories[Category] ) = "CAT1",
    VAR Val = 
    CALCULATE( 
        SUM( Data[Amount] ), 
        TREATAS( VALUES( CitiesFilter[City] ), Cities[City] ),
        VALUES( Cities[City] )
    )
    RETURN IF( ISBLANK( Val ), 0, Val ),
    SUM( Data[Amount] )
)

[v-yulgu-msft · Accepted solution]

Hi @smpa01,

You could new a calculated table which should be unrelated to source table. And later, please drag field from this new table to slicer.

Slicer Table = VALUES(Table1[City]) 

Add below measure and Table1[CAT2] into visual.

Measure for CAT1 = IF(SELECTEDVALUE(Table1[City])=SELECTEDVALUE('Slicer Table'[City]),SUM(Table1[CAT1]),0)

Best regards,

Yuliana Gu

[LivioLanzo · Accepted solution]

Hi @smpa01

You are going to have to unpivot your table like this (Power Query is your friend):

Then, build the below model, the Cities Filter table will be the one used in the slicer:

Thent his measure should do what you want:

Measure = 
IF(
    SELECTEDVALUE( Categories[Category] ) = "CAT1",
    VAR Val = 
    CALCULATE( 
        SUM( Data[Amount] ), 
        TREATAS( VALUES( CitiesFilter[City] ), Cities[City] ),
        VALUES( Cities[City] )
    )
    RETURN IF( ISBLANK( Val ), 0, Val ),
    SUM( Data[Amount] )
)

[smpa01]

@LivioLanzothank you very much for taking time to provide the solution. However, I do have a followup question for you if you can spare some time please.

How can I alter your code to show the following upon selecting city A from the slicer table. Instead of "zeroing" all the values for CAT1 for unselected cities, I want those values to turn to a "Blank".

City	CAT1	CAT2
A	100	-100
B		-200
C		-300
D		-400
E		-500

Thank you in advance.

[LivioLanzo]

Hi @smpa01

thx for the feedback.

The 0's you need them in order for the liner chart to work as you wanted. Therefore, you need to create a second measure which you will use inside the matrix visual

Measure = 
IF(
    SELECTEDVALUE( Categories[Category] ) = "CAT1",
    VAR Val = 
    CALCULATE( 
        SUM( Data[Amount] ), 
        TREATAS( VALUES( CitiesFilter[City] ), Cities[City] ),
        VALUES( Cities[City] )
    )
    RETURN Val,
    SUM( Data[Amount] )
)

[smpa01]

This is awesome. Thank you.