Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
79 | |
64 | |
62 | |
56 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |