Forum Discussion

PowerBI123456's avatar
PowerBI123456
Post Partisan
4 years ago
Solved

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 measure...
Need Help
Show and Tell
Tips and Tricks
  • HotChilli's avatar
    HotChilli
    4 years ago

    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.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

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 )