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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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
Employee
Employee

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


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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