Dax Calculation question

DAX DAX Calculations
1

Hi

I have a table where i need to count the rows of one column by a certain text string but then subtract the count of another column in that same table

To put it into context. I have a injured person table and i need to count the rows where the injury category = " Lost Time" but i need to deduct the number of injured people where they are a non employee, there is a column in the same table labeled ‘Contractor’ but it is a free text field so i need to identify where there is text in this column and then count the rows to deduct from the total rows counted where Injury Category = Lost Time.

I have used Countrows to calculate the number of Lost Time injuries but i cannot work out how to factor in deducting a count of the Contractor column if there is text in that column, I think i need to add the IF function but im not sure how?

This is what i have so far

LTI = Countrows( Filter(‘injured workers’ ,‘injured workers’[injury classification] = “Lost Time”))

Thanks for your help in advance

2

@AoakeP,

Without a PBIX file it’s difficult to provide a really specific solution, but I think I can provide you a helpful general framework for solving this.

Rather than trying to do this via one complex measure, I would break it into components using the following approach:

Step 1: create a calculated column, using the DAX SEARCH() function to determine row by row if the Contractor column contains the text that would identify that record as a contractor. The final parameter of the SEARCH function lets you set the value if the search string is not found. Set this equal to 0, so if your contractor measure = 0 that means they are an employee.

Step 2: you are off to a good start with your measure approach. Now you just need to modify it as follows to remove the injured who are also contractors:

LTI =

COUNTROWS (
    FILTER (
        'injured workers',
        'injured workers'[injury classification] = "Lost Time" &&
	'injured workers'[is a contractor] = 0
    )
)

Hope this gives you what you need. If not, please post your PBIX and I’d be glad to work through more a more specific solution with you.

  • Brian
3

Hi

Thanks Brian

This is very helpful and i did wonder if i needed to seperate out the measures, my issue is the contractor column is a free text field so all fields in that column that have any type of text i would need to identify in the calculated search column. I can’t attach the file as it has sensitive information but i can attach a screen shot of the column in the table

See attached
image002.png

4

@AoakeP,

For some reason, the image didn’t come through properly. Can you please resend?

Thanks.

  • Brian
5

image002

6

@AoakeP,

Which entries are contractors and which are employees? Is everyone not explicitly listed as “Employee” a contractor?

  • Brian
7

Hi Brian

Yes sorry i should have looked closer, its a free text field so the information is totally dependant on what end users enter however yes we can assume that all entries other than the blank cells and cells with the entry employee are contractors

8

@AoakeP,

Well, in that case we can avoid the use of SEARCH altogether, and use my favorite DAX function instead via a calculated column:

Is a Contractor =

SWITCH( TRUE(),
    SELECTEDVALUE( 'Injured Workers'[Contractor] ) = BLANK(), 0,
    SELECTEDVALUE( 'Injured Workers'[Contractor] ) = "Employee", 0,
    SELECTEDVALUE( 'Injured Workers'[Contractor] ) = "employee", 0,
    1
)

So, now per post #2 above, ‘injured workers’[is a contractor] = 0 will filter out all contractors/nonemployees.

Here’s an excellent video on SWITCH(TRUE()):

Good luck, and give a shout if you have any problems.

  • Brian
9

Thanks Brian really appreciate your time helping to solve this problem for me…I did check out the switch video I try to research and solve the problems myself before coming to the forum but I struggle with the structure of the dax formulas and when to apply them but practise hopefully will make perfect :slight_smile: I have a deadline of Friday for this report im working on so you have been a life saver

Thanks again
Best regards
Paula

10

@AoakeP,

My pleasure – glad to hear that was what you needed. I don’t know if there’s such a thing as perfect where DAX is concerned, but practice definitely makes progress. Keep at it, and I guarantee the concepts you’re struggling with now will all start to click into place.

Best of luck with your Friday deadline. If you run into any other troubles before then, lots of knowledgeable folks here eager to help.

  • Brian
1 Like
11

Hi Brian

Do I use the Custom Column function in the query editor to create my calculated column, i tried but a Expression Error message displayed saying"The name “Switch” isn’t recognised. Make sure it is spelled correctly". Should i be creating the calculated column in this way?

Cheers
Paula

12

@AoakeP,

No, Query Editor uses a different language (M) and won’t recognize DAX commands. Instead, click on the table you want to add the column to in the Fields view on the right side of your screen, then click the Modeling tab and “New Column”. Then go to the editor window below and paste in your DAX SWITCH code. You may need to click the down arrow (3, on the screenshot below) to see it all, but once you got it entered, hit enter or click the check mark and the calculated column should be added to your selected table. Click on the table view just to check and make sure it calculated correctly.

image
image.png1385×233 30.2 KB

Hope this is helpful.

– Brian

13

Thanks Brian

The calculated column worked in the table but the result produced a 0 for those items where the Contractor column fields were not blank or did not have the text “Employee” i expected those fields to display a 1 in the calculated column. i’ve attached a picture of the calculation and the results

14

image001 image002
Is a Contractor =

Switch(True(),

Selectedvalue(‘Injured Workers’[Contractor])=Blank(),0,

Selectedvalue(‘Injured Workers’[Contractor])=“Employee”,0,

Selectedvalue(‘Injured Workers’[Contractor])=“employee”,0,

1

)

15

@AoakeP,

Try the revised version below, without SELECTEDVALUE. Since a calculated column already has row context, that function shouldn’t be needed:

Is a Contractor =
SWITCH (
    TRUE (),
    Injured Workers[Contractor]
        = BLANK (), 0,
    Injured Workers[Contractor] = "Employee", 0,
    Injured Workers[Contractor] = "employee", 0,
    1
)
  • Brian

P.S. tested the syntax above on actual data using the code below to add a calculated column to a date table – worked fine.

Is Winter =

SWITCH(
    TRUE(),
    Dates[Month] = "Jan", 1,
    Dates[Month] = "Feb", 1,
    Dates[Month] = "Mar", 1,
    0
)
16

Genius, thank you Brian I now have a accurate report visual.

17

@AoakeP,

Excellent – so you’re all set for your Friday deadline?

  • Brian
18

Hoping to be I’m working on a custom human body visual that seems to be slowing my report down I lost an hours work so having to redo :slightly_frowning_face: