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.
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
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] ) )
)
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
that doesnt work
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]) )
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.
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.
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |