Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
I am trying to sum distinct values from amount_total column based on state and invoice_status columns. State should be "draft" and invoice_status should be "no"
Answer: 200000
Thanks for all your help
Solved! Go to Solution.
create a measure like
Sum of distinct values =
var distinctAmounts = CALCULATETABLE(VALUES('Table'[amount_total]),
TREATAS( { ( "draft", "no") }, 'Table'[state], 'Table'[invoice_status])
)
return SUMX(distinctAmount, [amount_total])
Hi John,
Really appreciate your help in solving my problem. What is TREATAS.
Your solution worked great.
Thanks again
TREATAS is a means of moving or creating filters. If you want to filter a number of columns by different values then you can create a table using {} table constructor and then use TREATAS to say this column of this table matches the first value, this column of that table matches the 2nd value etc.
Also handy if you want to move an existing filter from one table to another, e.g. TREATAS(VALUES('Table1'[id]), 'Table2'[id])
create a measure like
Sum of distinct values =
var distinctAmounts = CALCULATETABLE(VALUES('Table'[amount_total]),
TREATAS( { ( "draft", "no") }, 'Table'[state], 'Table'[invoice_status])
)
return SUMX(distinctAmount, [amount_total])
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |