CODEWITHSUNDEEP
powerbi
n179911a
n179911a

Reputation: 155

How to create 1 slicer to filter 2 columns on a table

I have a table which has 2 columns with has same values.

Date Source Destination
Nov 2022 New York London
Nov 2022 London Chicago
Nov 2022 Chicago Seattle
Nov 2022 Seattle New York
Nov 2022 Paris London

I create a Dimension table which has the set of cities between Source & Destination.

CityDimension
New York
Seattle
London
Chicago
Paris

How can I create a slider which filter both Source and Destation? e.g. if Seattle is selected in the slider, these rows will show

Date Source Destination
Nov 2022 Chicago Seattle
Nov 2022 Seattle New York

Upvotes: 0

Views: 143

Answers (1)

Marcus
Marcus

Reputation: 3995

If your CityDimension is related to your table

For this case, you can establish two relationships to the table, one will be active (default) and one will be inactive (activated when needed), and you will add a measure that returns a value if it is valid in the chosen selected city context.

It is up to you whether CityDimension should by default filter 'Table'[Destination] or 'Table'[Source]. In my model, the active relationship is between CityDimension and Source, and the passive is to Destination.

enter image description here

You will need to adjust your DAX according to your active/passive relationships, but you can define a measure like this to use as a filtering basis:

Show = 
VAR _dst =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( CityDimension[Destination] , 'Table'[Destination] ) // Uses PASSIVE relationship
    )
VAR _src =
    CALCULATE ( COUNTROWS ( 'Table' ) ) // Uses ACTIVE relationship
RETURN
    IF ( _dst || _src, 1 )

This measure must then be physically added to your table:

enter image description here

Depending on your requirements this may be inelegant! You can consider naming the table visual column something short like # and set conditional formatting on the column to match the column background and font, but it is difficult to hide it altogether.

If your CityDimension is not related to your table

For this case, it is required that your CityDimension table is unrelated to your fact table. If this is the case, you can add a measure that filters the table based on the selection in CityDimension using TREATAS to propagate your filters properly from dimension to fact:

Show =
VAR _dst =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        TREATAS ( VALUES ( CityDimension[Destination] ), 'Table'[Destination] )
    )
VAR _src =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        TREATAS ( VALUES ( CityDimension[Destination] ), 'Table'[Source] )
    )
RETURN
    IF ( _dst || _src, 1 )

Add this measure as a filter to the table visual, and as with the other solution, set it to Show is 1, but for this solution the measure does not have to be in the table itself, so it is perhaps a better one, depending on your requirements:

enter image description here

You can always consider adding this "loose" single-column table to your data model to enable this specific use-case.

Upvotes: 1

Related Questions