Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Countrows / if exists (by group)

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!

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous

     

    You may refer to below measure:

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

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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