Filtering a column based on another column value using DAX measure (Power BI)

Question

I have a slicer that filters a list based on names, the same table that the slicer references has a column for sport, i want to filter the names in a table based on the sport that corresponds the selected name on the slicer i.e if the name chosen plays a particular sport, i want the table to show the list of all the names that play the same sport as the selected name

I tried using this measure as a filter (I'm new to power Bi in case it looks crazy) and it didn't work: Measure = IF( HASONEVALUE('General info'[Name]), MAX( CALCULATETABLE( FILTER('General info', 'General info'[Sport] = SELECTEDVALUE('General info'[Sport])), ALL('General info'[Name]) ) ), BLANK() )

So each name in the table will only ever be associated with one sport, never multiple sports? — Jos Woolley
– Jos Woolley, Commented Apr 28, 2023 at 18:12
Yes each distinct name will always be only associated with one sport — Jason Odongo
– Jason Odongo, Commented Apr 28, 2023 at 20:44

Jos Woolley · Accepted Answer · 2023-04-29 06:35:44Z

0

You'll have to create a separate table of names to use for the slicer, a table which is unrelated to your main table. For example, assuming you create a new table called Names which comprises a single column (called Name) of distinct names, you can then create the following measure:

=
VAR ChosenName =
    MIN ( Names[Name] )
VAR SportPlayedByChosenName =
    CALCULATE ( MIN ( 'Table'[Sport] ), 'Table'[Name] = ChosenName )
RETURN
    IF ( SUMX ( 'Table', 0 + ( 'Table'[Sport] = SportPlayedByChosenName ) ), 1, 0 )

You then add a slicer based on the Names table to the canvas, and create your chosen visual using the Name field from your main table, dragging the above measure into the filters pane for that visual, with the condition that it be equal to 1.

edited Apr 29, 2023 at 6:35

answered Apr 29, 2023 at 6:20

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filtering a column based on another column value using DAX measure (Power BI)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related