Skip to main content
cancel
Showing results for 
Search instead 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

Reply
bvarian
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.

 

exampledata.png

 

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
Dhairya
Solution Supplier
Solution Supplier

Hey @bvarian 
Please follow the below steps to achieve your expected output

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
 
Dhairya_0-1689141311686.png

 

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:
Dhairya_1-1689141384106.png

 

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!

View solution in original post

1 REPLY 1
Dhairya
Solution Supplier
Solution Supplier

Hey @bvarian 
Please follow the below steps to achieve your expected output

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
 
Dhairya_0-1689141311686.png

 

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:
Dhairya_1-1689141384106.png

 

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.