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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PowerBI123456
Post Partisan
Post Partisan

Help with example - filtering for more than 1 dimension

Hi - Appreciate anyone help.

 

Attached is a simple example. I have 2 tables, one is my FACT which has the accounts with codes and the other is my DIM which are the codes. I am trying to do measures to show how many/which accounts have specific codes.

PowerBI123456_0-1633541119376.png

For example, I am calculating the measure below. 

 

Code Measure = CALCULATE([Accounts],and(DIM[Code]="1",'DIM'[Code]="2"))

 

It shows as blank even though I know account A has codes 1 and 2. Pleaese help!

 

PBI File 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

What about this:

Ch Measure = 
VAR ones = SELECTCOLUMNS(FILTER('FACT', RELATED(DIM[Code]) = "1"), "Account", 'FACT'[Account ])
VAR Twos = SELECTCOLUMNS(FILTER('FACT', RELATED(DIM[Code]) = "2"), "Account", 'FACT'[Account ])
VAR onesAndTwos = INTERSECT(ones, Twos)
RETURN
COUNTROWS(onesAndTwos) 

 

please test at your side.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Code Measure =
VAR newtable =
ADDCOLUMNS (
VALUES ( 'FACT'[Account ] ),
"@one", CALCULATE ( COUNTROWS ( FILTER ( 'FACT', 'FACT'[Code] = "1" ) ) ),
"@two", CALCULATE ( COUNTROWS ( FILTER ( 'FACT', 'FACT'[Code] = "2" ) ) )
)
VAR filternewtable =
FILTER ( newtable, AND ( [@one] <> BLANK (), [@two] <> BLANK () ) )
RETURN
COUNTROWS ( filternewtable )

 

Picture1.png

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
HotChilli
Super User
Super User

What about this:

Ch Measure = 
VAR ones = SELECTCOLUMNS(FILTER('FACT', RELATED(DIM[Code]) = "1"), "Account", 'FACT'[Account ])
VAR Twos = SELECTCOLUMNS(FILTER('FACT', RELATED(DIM[Code]) = "2"), "Account", 'FACT'[Account ])
VAR onesAndTwos = INTERSECT(ones, Twos)
RETURN
COUNTROWS(onesAndTwos) 

 

please test at your side.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.