cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

Hey @bvarian

Step1: Create a calculated column using the below DAX

`start with charges_to_account = MID(TableA[charges_to_account],1,3)`

You will get the following output

Step2: Create a measure using the below DAX

`Summarized 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!
Super User

Hey @bvarian

Step1: Create a calculated column using the below DAX

`start with charges_to_account = MID(TableA[charges_to_account],1,3)`

You will get the following output

Step2: Create a measure using the below DAX

`Summarized 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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.