Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
Hey @PowerBI123456 ,
I was wondering if my measure worked and solved your problem?
@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?
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |