topic Re: Filtering on multiple rows in DAX Commands and Tips

Filtering on multiple rows

GrowingPeak — Wed, 08 Mar 2023 13:38:18 GMT

Hello everyone,

I have a database with the names of the project in each row and a list of porperties in the headings, if a specific project has a particular property there is a 1, otherwise the cell is null. How can I create a filter on the report tab with three options corresponding to the three properties that selects only the rows with the specific property selected? Below there is an example of the table.

Thank you

Re: Filtering on multiple rows

FreemanZ — Wed, 08 Mar 2023 13:55:44 GMT

not so sure about your expectation. could you elaborate it?

Re: Filtering on multiple rows

GrowingPeak — Wed, 08 Mar 2023 14:37:27 GMT

Sure! I need a filter like this:

where when I select, for example, Property 1, it displays data on a table like this:

Projects	Property 1	Property 2	Property 3
A	1
D	1		1

Thank you

Re: Filtering on multiple rows

FreemanZ — Wed, 08 Mar 2023 22:18:58 GMT

hi GrowingPeak

you can directly plot a table visual as it is and a slicer with Column1.

p.s. please consider @someone to continue a discussion.

Re: Filtering on multiple rows

GrowingPeak — Thu, 09 Mar 2023 08:32:17 GMT

Thank you! And how can I link this filter to a map instead? Where I have different locations instead of the projects and I want to filter the locations based on the property they have?

Thank you

Re: Filtering on multiple rows

Anonymous — Tue, 14 Mar 2023 02:20:17 GMT

Hi GrowingPeak ,

Please try below steps:

1. below is my test table

Table:

Table2:

2. please create a measure with below dax formula

Measure = VAR _property = SELECTEDVALUE ( Table2[Property] ) VAR _project = SELECTEDVALUE ( 'Table'[Projects] ) RETURN SWITCH ( TRUE (), _property = "Property1", IF ( CALCULATE ( MAX ( 'Table'[Property1] ), 'Table'[Projects] = _project ) = 1, 1 ), _property = "Property2", IF ( CALCULATE ( MAX ( 'Table'[Property2] ), 'Table'[Projects] = _project ) = 1, 1 ), _property = "Property3", IF ( CALCULATE ( MAX ( 'Table'[Property3] ), 'Table'[Projects] = _project ) = 1, 1 ), ISBLANK ( _property ), 1 )

3. add a slicer with Table2 field, add a table visual with Table field, add measure to table visual filter pane and apply

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Filtering on multiple rows

GrowingPeak — Tue, 14 Mar 2023 08:24:31 GMT

Thank you! I will try and let you know.