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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

 Column 1 Column 2 Flag A B 1 B C 1 D A 1 E D 1

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 1 Column 2 Flag A B 1 B C 1

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 B 1

However, I want my visual to look like below

 Column 1 Flag A 1 B 1

Can somebody please help?

1 ACCEPTED SOLUTION
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.

10 REPLIES 10
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.

Frequent Visitor

It works, thank you

Kudo Kingpin

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.

Frequent Visitor

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

Kudo Kingpin

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

Frequent Visitor

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

Responsive Resident

Is "flag" a numeric column?

Frequent Visitor

yes

Super User

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!

Frequent Visitor

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors