Writing a DAX function to filter out multiple criteria for a given product

DAX DAX Calculations
1

Hi there,

I am a new user to both the Enterprise DNA site and a newbie when it comes to PowerBI and DAX!

I have been tasked to create a report that shows the sum of each of our products revenue/ship amount by fiscal year, month etc. We are an HVAC company and have several air conditioning products that all have different nomenclature for the product code string (usually 17-20 characters long)

I would like to create a measure that sums up our revenue for each of these products. The issue is that while we have one large database, it lumps all of the products, accessories, and parts together. So far I have created a page in Power Bi for each of our products and used the page level filter function to show the relevant data for that specific product only.

This table is what I have created so far (with the specific customer and product info blurred out).

data%20table_Products_Shipments_Power_BI_Desktop
data table_Products_Shipments_Power_BI_Desktop.jpg1855Ă—794 226 KB

As you can see, this works okay to create a table for a specific product, but when I need to use any measures or drill down into the data more , I need a more comprehensive solution.

How can I create a DAX measure that takes into account multiple criteria like (“starts with XXX”, “does not contain XXX”, but only by using DAX to sum the correct ship amount ( for each product, without having other products contaminate that number?

Thanks!!

2

@tomtom62,

Welcome to the forum! I think you’ll find this to be a terrific resource and a wonderful community.

If I understand your requirement correctly, this can be handled via a standard CALCULATE/FILTER construct using some text functions: LEFT() and CONTAINSSTRING() to generate your filter conditions. Here’s what the DAX measure would look like:

Filtered Total Sales = 
    CALCULATE(
        [Total Sales],
        FILTER(
            Products,
            LEFT( Products[Product Name], 1) = [Harvest Starts With] &&
            CONTAINSSTRING( Products[Product Name], [Harvest Does Not Contain] ) = FALSE 
        )
    )

image
image768Ă—557 23.7 KB

As a newcomer to DAX, I would very strongly recommend that you go through the lessons below really thoroughly, focusing specifically on understanding evaluation, filter and row context. It takes everyone a while to truly understand these concepts (and Sam has a lot more great videos that go further in depth on these topics), but without that understanding it’s difficult to do anything useful in DAX.

I hope this is helpful to you. Full solution file posted below.

2 Likes