Countrows / if exists (by group)

[Anonymous]

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!

[v-cherch-msft]

Hi @Anonymous

You may refer to below measure:

Measure  =
VAR a =
    CALCULATE ( COUNTROWS ( 'Fact' ), ALL ( 'Dimension' ) )
RETURN
    SUMX ( 'Dimension', a )

Regards,

Cherie

[Anonymous]

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.

[v-cherch-msft · Accepted 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

[Anonymous]

@v-cherch-msft

Measure 2 seems to be working. I'll run more tests, but looks good.

Thank you.