Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a standard dimension-fact-relationship. Dimension has a hierarchy (level1, level2). Level1 is the field that is attacted to the fact table.
Now I need a row count from the fact table, but so that every level1 value that is under level2 gets the same count. The actual count is irrelevant, basically I need "exists" type of a deal.
For clarification, If I do simple countrows(fact[level1]) I might get the following result:
Level2: 10
Level1-1: 0
Level1-2: 2
Level1-3: 0
Level1-4: 8
But what I need is:
Level2: 40
Level1-1: 10
Level1-2: 10
Level1-3: 10
Level1-4: 10
This has to be a measure, can't be calculated column.
Thanks!
Solved! Go to Solution.
Hi @Anonymous
You may try to use below measure. If it is not your case, please share your data sample file. You can upload it to OneDrive or Dropbox and post the link here.
Measure 2 = VAR a = CALCULATE ( COUNTROWS ( 'Fact' ), ALL ( 'Dimension'[Level1] ) ) RETURN SUMX ( 'Dimension', a )
Regards,
Cherie
Hi @Anonymous
You may refer to below measure:
Measure = VAR a = CALCULATE ( COUNTROWS ( 'Fact' ), ALL ( 'Dimension' ) ) RETURN SUMX ( 'Dimension', a )
Regards,
Cherie
Hey @v-cherch-msft
Your suggestion doesn't quite work or maybe I just wasn't clear enough with the problem.
What your measure returns is this:
Level2-1: 40
Level1-1: 10
Level1-2: 10
Level1-3: 10
Level1-4: 10
Level2-2: 20
Level1-5: 10
Level1-6: 10
Same value for each and every level1 regardless of the level 2. Where in practice I need a measure that returns same value for each level1 within the same level2. Like this.
Level2-1: 40
Level1-1: 10
Level1-2: 10
Level1-3: 10
Level1-4: 10
Level2-2: 4
Level1-5: 2
Level1-6: 2
I appreciate the effort though.
Hi @Anonymous
You may try to use below measure. If it is not your case, please share your data sample file. You can upload it to OneDrive or Dropbox and post the link here.
Measure 2 = VAR a = CALCULATE ( COUNTROWS ( 'Fact' ), ALL ( 'Dimension'[Level1] ) ) RETURN SUMX ( 'Dimension', a )
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |