Determine codes by account

[PowerBI123456]

Hi,

I have 2 tables. My fact table: showing which codes are on an account and my dimension table: showing the codes. Here is a sample file. Sample File 

I am trying to determine which accounts have codes A & B using a measure. I am using the measure below but my results are coming up as blank even though I know it should be 2 (accounts 1 and 2).  Please help. 

[selimovd]

Hey @PowerBI123456 ,

it took a few minutes, but the following measure should give you the desired result:

Amount Code A & B = 
// Summarize all rows with Activity Code A or B
VAR tTable =
    SUMMARIZE(
        FILTER( 'FACT: Activity', 'FACT: Activity'[Code] IN { "A", "B" } ),
        'FACT: Activity'[Accounts],
        'FACT: Activity'[Code]
    )

// Group results by amount of rows
VAR tGroupBy =
    GROUPBY(
        tTable,
        'FACT: Activity'[Accounts],
        "Row Counts", COUNTX( CURRENTGROUP(), 1 )
    )

// Filter result to results with row count = 2 (Means A and B)
// And count the amount Accounts that have A and B
VAR result =
    COUNTX( FILTER( tGroupBy, [Row Counts] = 2 ), [Row Counts] )
RETURN
    result

I added a few comments to make it more understandable.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[selimovd]

Hey @PowerBI123456 ,

I was wondering if my measure worked and solved your problem?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[PowerBI123456]

@selimovd Apologies for the delay in response, it does work! However, my activity file is super large, 10+ million rows. Anyway to use the dimesnion table to filter instead for better performance?