Conditional value in Filtering context

[sebouier]

Hi,

Here's my table :

Id	Name	Cat
1	Bob	a
2	Allan	b
3	Allan	c
4	Jason	e
5	Jason	f

User is selecting cat b and f so it shows row number 2 and 5.

How to show a table with rows number 2, 3, 4, 5 (because of the identical names) and also have a flag on rows 2 and 5?

Many thanks for your help.

[Anonymous · Accepted solution]

Hi @sebouier ,

@AnkitaaMishra Thanks for your reply!

And @sebouier , please try this way:
First use this DAX to create a new table for slicer:

Slicer = VALUES('Table'[Cat])

Note: without any relationship!

Then use this DAX to create a measure:

Measure = 
VAR _Cat = VALUES(Slicer[Cat])
VAR _Name = MAX('Table'[Name])
RETURN
IF(
    CALCULATE(
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[Name] = _Name && 'Table'[Cat] IN _Cat
            )
        ),
        ALLSELECTED('Table')
    ) > 0,
    1,
    0
)

Set it up as shown in the following figure:

Then use this DAX to create another measure:

Flag = 
IF(
    MAX('Table'[Cat]) IN VALUES(Slicer[Cat]),
    1,
    0
)

And put this measure into the table visual:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitaaMishra]

hi @sebouier ,

If my understanding is clear and below is the expected output for your problem statement : 

Then please follow below steps : 

• Create a summarize table : 
  
• Create a many to many relationship with your originaltable and summarized table based on NAME column
  
• Use the slicer for CAT from summarize table 
  
• create the table visual by using columns from your originaltable (All the columns in table visual are coming from OriginalTable) 
  
  
  If this approach works for you, please select it as solution. 

Thanks,

Ankita

[sebouier]

Thanks for your quick help @AnkitaaMishra . 

Your approach is very elegant and smart.

My approach was slightly different because I duplicated the Originaltable into Copytable. And I created a similar relation as yours: Originaltable.Name -> Copytable.Name

Then I displayed on a visual table the fields from Copytable.

But my real struggle is to be able to identify the filtered row in Originaltable.

How would you proceed to add a column as a "filtered row flag"?

Result expected : 

Id	Name	Cat	filtered row flag
2	Allan	b	1
3	Allan	c	0
4	Jason	e	0
5	Jason	f	1

[foodd]

Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.