Forum Discussion
Summarize based on multiple filtered criteria using both match and starts with
Struggling with a DAX formula for a Measure to summarize data from a dataverse table based on multiple filter criteria. Hoping someone might be able to provide some guidance.
The scenario:
I need to summarize the value in the amount column when the type equals "sale" and the charges_to_account starts with "abc".
Based on that, the highlighted values below would be the ones totaled, to return a summed value of $120.00.
Seems simple, but I've tried multiple ways to nest the criteria using CALCULATE with different syntax using FILTER, SUM or SUMX and I keep being told that either the syntax is incorrect, I'm using the incorrect calculation method or there are either not enough or two few criteria for the function. I even tried letting it propose DAX code based on plain-language input.
Appreciate any help/guidance. I'm not a total DAX noob, but am far from expert, and this one seemed simple but is leaving me scratching my head. I'll be using the results of this Measure later in other calculations so getting it right is a foundational element for a dashboard.
Hey bvarian
Please follow the below steps to achieve your expected output
Step1: Create a calculated column using the below DAXstart with charges_to_account = MID(TableA[charges_to_account],1,3)
You will get the following output
Step2: Create a measure using the below DAXSummarized amount =
CALCULATE(
SUM(TableA[amount]),
ALLEXCEPT(
TableA,
TableA[type],
TableA[start with charges_to_account]
)
)You will get the following output:
If this helps you then please mark my solution as accepted so that others can find it quickly when they face same issue. Thank You!
1 Reply
- Dhairya
Solution Supplier
Hey bvarian
Please follow the below steps to achieve your expected output
Step1: Create a calculated column using the below DAXstart with charges_to_account = MID(TableA[charges_to_account],1,3)
You will get the following output
Step2: Create a measure using the below DAXSummarized amount =
CALCULATE(
SUM(TableA[amount]),
ALLEXCEPT(
TableA,
TableA[type],
TableA[start with charges_to_account]
)
)You will get the following output:
If this helps you then please mark my solution as accepted so that others can find it quickly when they face same issue. Thank You!