Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
What DAX formula can I use to only calculate values/numbers based on a calculated column in the fact table being "0" or "1" (true/false format)
I have a fact table which shows unique account numbers, transaction ids etc.
It was a long merge process in power query to get to the result that will allow me to build one fact table to use in front end,.
In order to compare the facts some of the transaction IDs are duplicate (have 4 rows etc)
How can I then build a measure that will count the value of this transaction only once in the measure.
Example of fact table shown below:
Have only 4 unique transactions in total - the value sold should only be 699, but with every DAX measure I try it is triple counting 99 and therefore giving me 897.
Moreover my second argument is that I only want to sum Unique transactions which Sold After Interaction so I am looking for help in creating a DAX Measure (maybe using var) that will filter Unique Trans IDs and Sold After interaction > 0 and then Sum the Column "Value of Sold"
Trans ID | Value of Sold | Sold after Interaction |
101 | 99.00 | 1 |
101 | 99.00 | 1 |
101 | 99.00 | 1 |
102 | 150.00 | 1 |
103 | 200.00 | 0 |
104 | 250.00 | 1 |
Any help / ideas would be appreciated.
Solved! Go to Solution.
Hi @aggiebrown ,
Try the following formula:
Measure =
SUMX(
DISTINCT(
FILTER(
'Table',
'Table'[Sold after Interaction] > 0
)
),
'Table'[Value of Sold]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aggiebrown ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
@aggiebrown , Try a measure like
Sumx(summarize(filter(Table, Table[Sold after Interaction] >0), [Trans ID], [Value of Sold]),[Value of Sold])
@amitchandak many thanks for your suggestion. I have tried it however PBI does not like the "> 0 " bit and underlines it with red, so can't proceed. When I get rid of the "> 0 " condition the measure just comes back as 0.00
Hi @aggiebrown
Try this measure:
Sum of transactions =
SUMX (
GROUPBY (
FILTER ( 'Table', 'Table'[Sold after Interaction] > 0 ),
'Table'[Trans ID],
'Table'[Value of Sold],
'Table'[Sold after Interaction]
),
'Table'[Value of Sold]
)
The output will be as below:
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!
Hi, many thanks for your reply. I have tried that and it does not seem to work.
It's worth saying I have a relationship created in the model that links back to the specific agent but I don't see how that would cause an issue.
Hi @aggiebrown ,
Try the following formula:
Measure =
SUMX(
DISTINCT(
FILTER(
'Table',
'Table'[Sold after Interaction] > 0
)
),
'Table'[Value of Sold]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |