DAX Calculation with Filters

DAX DAX Calculations
1

Hi

I have a table that i want to sum the total hours but need to filter on the year 2020 and Divisions “Collections, Post Collections, Coporate” and Technical Services"

I have written the below calculation and it worked when filtering just on the year but when i added the Divisons in the calculation it failed.

Total Hours Worked 2020 = SUMX(
filter(‘Hours Worked’,‘Hours Worked’[Year]= 2020 && ‘Hours Worked’[Division] = “Collections” && ‘Hours Worked’[Division]= “Post Collections” && ‘Hours Worked’[Division] = “Corporate” && ‘Hours Worked’[Division] = “Technical Services”),
‘Hours Worked’[Hours Year])

2

@AoakeP,

In debugging these sorts of problems, it’s enormously helpful to format your DAX code. Once I did that, the problem immediately jumped out at me:

Total Hours Worked 2020 =

SUMX (
    FILTER (
        'Hours Worked',
        'Hours Worked'[Year] = 2020
            && 'Hours Worked'[Division] = "Collections"
            && 'Hours Worked'[Division] = "Post Collections"
            && 'Hours Worked'[Division] = "Corporate"
            && 'Hours Worked'[Division] = "Technical Services"
    ),
    'Hours Worked'[Hours Year]
)

You are using “AND” conditions for your filter via the “&&”. This means that all five filter conditions have to be true for a record to be included in the filter table. Since some of these categories are mutually exclusive (e.g., collections and non-collections), no record will meet all the conditions stated, and thus the filter table will be empty.

The way I would structure this instead is to set a variable first using SWiTCH that returns a 1 if ANY of the divisional conditions are met (i.e., an OR construct, not an AND), and a 0 otherwise Then the main measure can use an AND construct to filter where year equals 2020 and the variable equals 1.

If you can provide your PBIX file, I’d be happy to write out the specific DAX using the approach outlined if that’s not a specific enough answer above.

I hope that’s helpful.

  • Brian
3

Hi Brian :slight_smile:

Im unable to attach the pbix file as it has sensitive company information, i have tried to copy and paste a screen shot of the table image but for some reason i can not attach.

Would it be possible to have some guidance on how to write the variable using switch with the infomation i have provided.

4

@AoakeP,

OK, understood. Let me work up an example using some nonsensitive sample data I have. Will get back to you shortly…

  • Brian
5

Great thanks Brian, if it of any help the table i am using is the same table i was working on yesterday that you helped me solve the date column issue.

Im not sure why i can’t attach today

6

@AoakeP,

This general structure should work for you. It works better with the SWITCH statement as a separate measure (Product ID in my data set will be equivalent to Division in yours):

IncludedInFilter = 

SWITCH( SELECTEDVALUE( Products[Product ID] ),
    "ENX2000", 1,
    "ENX2005", 1,
    "ENX2024", 1,
    "ENX2025", 1,
    0
) 

Filtered Total Sales by Product and Year = 

CALCULATE(
    [Total Sales],
    FILTER(
        Dates,
        Dates[Year] = 2020
    ),
    FILTER(
        Products,
        [IncludedInFilter]= 1
    )
)

Full solution file attached. Just give a shout if you have any problems.

7

Hi Brian

It worked!

It was trial and error from my end but it was good learnings. I couldnt get the formula to work but i went back and checked my table data types and data model links and after a number of changes i finally got it to work.

It is a good example of where your data model structure is so important
Heres what the successful calculation looks like

Hours Worked 2020 Employess =

CALCULATE(

[Total Hours Worked],

FILTER(

‘Date Table’,

‘Date Table’[Year]= 2020

),

Filter(

Divisions,

[IncludedinHrsFilter]=1

)

)

image

8

@AoakeP,

Great – glad it worked for you. You hit on exactly why we always prefer to see the PBIX file when working through solutions. Often I will see a question to the effect of “why doesn’t my DAX work properly?”, and it turns out that the DAX code indeed is flawless, but the data model is flawed to the extent that DAX will never return correct results. This isn’t so bad when the DAX returns obvious nonsense results, but in some cases I’ve seen it return wrong results due to data modeling problems that are not obviously wrong without careful checking. That’s the truly scary scenario…

  • Brian
2 Likes