DAX get the total number of rows when there are two dimensions. dim1 can have many rows in dim2

[DebbieE]

Say im taking this as an example

Year       Key1     Key2        Key1Count     Key2Count

2017      1           1                1                   1

2017      1           2                 1                   1

2017      1           3                 1                   1

2017       1          4                 1                   1

Total                                     1                    4

Key1 Count is a Distinct Count of Key1 in the fact table and Key2  count is a distinct count of Key2

So I want a measure that does the following

Year       Key1     Key2        Key1Count     Key2Count      TotalofKey2

2017      1           1                1                   1                      4

2017      1           2                 1                   1                     4

2017      1           3                 1                   1                     4

2017       1          4                 1                   1                     4

Total                                     1                    4

So each row shows there are 4 key 2s to Key 1

I dont know if its because its friday but I cant figure this out at the moment so any help would be massively appreciated

[v-eqin-msft]

Hi @DebbieE ,

According to my understand, you want to calculate the discount of key2 of each key1 , right?

You could use the following formula:

TotalofKey2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Key2] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Key1] = MAX ( 'Table'[Key1] ) )
)

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[AlB]

Hi @DebbieE 

Try

COUNTROWS( ALL (Table1[Key2]) )

[DebbieE]

that doesnt work

Collisions per Accident = COUNTROWS(ALL(NationalCas[CasualtyKey])

)

Whereas before I had this without this DAX

I have the two DAX Measures to get the Distinct Count of Key 1 and Key 2.

Add this

Measure to get total Key2 across all Key 1s = COUNTROWS(ALL(Table[Key2])

it just goes on and on and on. Its like its adding up every single Key 2 without taking into account the Key1 and every Key 2 appears in the table?

[AlB]

@DebbieE 

You want the number of Key2s that fall under the current Key1? Across all years? If not, what role does Year play? It would help if you showed a sample of the relevant tables and thier relationships.  Try:

CALCULATE( DISTINCTCOUNT( Table1[Key2] ), ALL(Table1[Key2]) )

[DebbieE]

yes I want a distinct count of all Key 2s against Key 1.

Year is only against Key 1.

That last bit of DAX doesnt work and also doesnt mention Key 1

Too many arguments were passed to the DISTINCTCOUNT function. The maximum argument count for the function is 1.

[AlB]

@DebbieE 

You must have entered something incorrectly.  There's only one argument to DISTINCTCOUNT in the code above:

Measure =
CALCULATE ( DISTINCTCOUNT ( Table1[Key2] ), ALL ( Table1[Key2] ) )

 I'd need a pbix (dummy data reproducing the problem if necessary) and the expected result to provide a more accurate answer.