cancel
Showing results for
Did you mean:
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.

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!

1 ACCEPTED SOLUTION
Super User

``````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) ``````

2 REPLIES 2
Super User

Code Measure =
VAR newtable =
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 )

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

Best regards, JiHwan Kim

Super User

``````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) ``````

Announcements