cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
esquiveljc
Frequent Visitor

Special filtering

Hello,
I have two tables (TableABC and TableQty) like the ones below, linked by column2.

TableABC                            

Column 1Column 2

AA1
AA2
AA3
AA4
BB1
BB2
BB3
BB4
CC1
CC2
CC3
CC4
CC5

 

TableQty

YearQtyColumn2

202154A1
2021948A2
2021245B3
2021241C5
202013C1
2020525C4
202023B2
2019135A4
2019246B2
2019245B4
201912C1
2019515C2
201968C3

I have a matrix which has Column1 and Column2 as rows, Year as Column and Qty as values. I also have Column2 in a segment filter.

2021-03-01 19-49-26.jpg

I would like that every time I select the filters A1 or A2 or A3 or A4, my matrix reacts like this:

2021-03-01 19-59-58.jpg

For B1 or B2 or B3 or B4 like this:

2021-03-01 20-00-12.jpg

I know that we get to the same result if I put Column1 as a segment filter, but in my case it is impossible.
Is there a way to do this?
Thank you very much!

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Here is one way to do it.  Use Col2 from your ABC table in the slicer and Col2 from the Qty table in the table visual, along with a measure like below to get the shown result.

mahoneypat_0-1614652503008.png

 

Show All Col2 Same Col1 =
CALCULATE (
    SUM ( TableQTY[Qty] ),
    ALL ( TableABC[Column2] ),
    VALUES ( TableABC[Column1] )
)

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Super User IV
Super User IV

Here is one way to do it.  Use Col2 from your ABC table in the slicer and Col2 from the Qty table in the table visual, along with a measure like below to get the shown result.

mahoneypat_0-1614652503008.png

 

Show All Col2 Same Col1 =
CALCULATE (
    SUM ( TableQTY[Qty] ),
    ALL ( TableABC[Column2] ),
    VALUES ( TableABC[Column1] )
)

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.