Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
FrailHumanity
Frequent Visitor

display all the rows of first column that were filtered by a second column

Column 1Column 2Flag
AB1
BC1
DA1
ED1

 

I have a table like this. I am able to filter the table by both the columns using a measure. For example, the output table for a filter on both columns 1 and 2 for 'B' looks like this 

Column 1Column 2 Flag
AB1
BC1

 

I want a visual that displays only Column 1 and Flag in a Matrix form. The visual I get at the moment 

 

Column 1 Flag
B1

 

However, I want my visual to look like below

 

Column 1Flag 
A1
B1

 

Can somebody please help?

1 ACCEPTED SOLUTION
ahmedoye
Responsive Resident
Responsive Resident

Assuming you have a Disconnected Table with values in both Column 1 and Column 2. If you have that already, good, other wise, you can create that like with a New Calculated Table like this:

CombinedValues =
UNION(VALUES('MyTable'[Column1]), VALUES('MyTable'[Column2])). Rename the column created here as "Slice"

Add the column from your disconnected slicer to the Filters Pane.

Then create a Measure for your flag as below

FlagDisplay =
CALCULATE (
    SUM ( 'MyTable'[Flag] ),
    KEEPFILTERS (
        'MyTable'[Column1]
            IN VALUES ( CombinedValues[Slice] )
                || 'MyTable'[Column2] IN VALUES ( CombinedValues[Slice] )
    )
)

 

If this works for you, please mark it as a solution so it can be found easily by anyone who may have similar issues.

View solution in original post

10 REPLIES 10
ahmedoye
Responsive Resident
Responsive Resident

Assuming you have a Disconnected Table with values in both Column 1 and Column 2. If you have that already, good, other wise, you can create that like with a New Calculated Table like this:

CombinedValues =
UNION(VALUES('MyTable'[Column1]), VALUES('MyTable'[Column2])). Rename the column created here as "Slice"

Add the column from your disconnected slicer to the Filters Pane.

Then create a Measure for your flag as below

FlagDisplay =
CALCULATE (
    SUM ( 'MyTable'[Flag] ),
    KEEPFILTERS (
        'MyTable'[Column1]
            IN VALUES ( CombinedValues[Slice] )
                || 'MyTable'[Column2] IN VALUES ( CombinedValues[Slice] )
    )
)

 

If this works for you, please mark it as a solution so it can be found easily by anyone who may have similar issues.

It works, thank you

joaoribeiro
Impactful Individual
Impactful Individual

Hi,

 

In this case I suggest you use a calculated column with the following formula:

Flag = IF('Table'[Column 1] = "B" || 'Table'[Column 2] = "B", 1, BLANK())

After that, add a filter in the visual using this field set to 1.
joaoribeiro_0-1680264901383.png
joaoribeiro_1-1680265527310.png

 

 

 

that's what I have in the 2nd table. I want a visual table that doesn't include Column 2 but still gives me all rows filtered in Column 1

Yes, please try to follow my suggestion and don't add the second column, it will work 😊

joaoribeiro_0-1680265342445.png

 



yes but this is dynamic, I want it to filter based on a Slicer

ahmedoye
Responsive Resident
Responsive Resident

Is "flag" a numeric column?

yes

Arul
Super User
Super User

@FrailHumanity ,

How many slicers do you have ?and what are all the columns you are using?

Thanks,

Arul





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


just one slicer which has combined values of both column 1 and column 2. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.