Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I have 2 tables a client table and a master table that are related. I want to calculate the number of clients thats $ is over 1M. I cant seem to get it, here are some example codes that dont work
CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]), MasterTable[$] > 1000000) This results in just the total number of clients in the data.
CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]), FILTER('MasterTable, CALCULATE(SUM('MASTERTABLE'[$]) >= 1000000))))
CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]), Filter(MasterTable, Calculate(sum(MasterTableJ[$]), allexcept(clienttable, masterclientname)) >= 1000000))
I even tried using a measure
IFMeasure = IF(SUM(MasterTable[$]) > 1000000, 1, 0)
Actual Measure = CALCULATE(DISTINCTCOUNT('ClientTable'[ClientName]),FILTER('MasterTable', 'IFMeasure'[1M] = 1))
Anyone have any examples/solution to this?
Hi,
Please check the below picture and the attached pbix file.
All measures are in the attached pbix file.
Clients count over 1600 : =
COUNTROWS ( FILTER ( Clients, [Sales :] > 1600 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
My $ and client are on two different tables. Also the table with the $ is based off individuals not clients. The tables are related.
User | Count |
---|---|
53 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |