Forum Discussion

PowerBI123456's avatar
PowerBI123456
Icon for Post Partisan rankPost 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 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!

 

PBI File 

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.

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    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
    Icon for Super User rankSuper 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 )