Using Blank() or "+0" in DAX disrupts Time Based Calculations

[bikram_laishram]

I have a table of personal injuries since 2016. The table gives details of the injury such as date/time, severity. I work in the maritime industry, where standard classification of injuries are as follows:

1. Fatality

2. Permenant Total Disability(Unable to work on ship and shore)

3. Permenant Partial Disability(Unable to work in ship only)

4. Lost Workday cases

5. Restricted Workday Cases

6. First Aid Cases

7. Medical Treatment Cases

We normally analyse Loss time injuries or LTI which is count of all injuries with following severities:

1. Fatality

2. Permenant Total Disability(Unable to work on ship and shore)

3. Permenant Partial Disability(Unable to work in ship only)

4. Lost Workday cases

After pubhlishing the first draft, someone pointed that we wanted to see LTI for all location, if blank then it should display zero

LTI 1= calculate([Injury Count], Filter('Incident Crew','Incident Crew'[Incident Severity] in {FAT,PPD,PTD,LWC}))

was changed to 

LTI 1= calculate([Injury Count], Filter('Incident Crew','Incident Crew'[Incident Severity] in {FAT,PPD,PTD,LWC})) + 0

This replaces all blanks with 0

My report has a date slicer which is set for ytd 2019. Inspite of that the report shows me all the years from 2016 with 0 and 2019 onwards it has the actual value. Similarly if I were to select 2018, it would show me all year but LTI values would only appear for 2018.

How can I write a dax in way that if I select a period it should allow only that value to reflect in a visual or a graph.

Inspite of selecting 2019, it shows me all the year and  is completly  for the end users.

Any suggestions to help with the above would be appreciated. I would still love to see a 0 for blank values but only for the period I select

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file with the Data Model.

[Anonymous]

HI @bikram_laishram ,

I'd like to suggest you add variables to store current value and selected value, then use if statement to compare with them.

LTI 1 =
VAR selected =
    SELECTEDVALUE ( Table[YTD] )
VAR curr =
    MAX ( 'Incident Crew'[Year] )
RETURN
    IF (
        curr = selected,
        CALCULATE (
            [Injury Count],
            FILTER (
                'Incident Crew',
                'Incident Crew'[Incident Severity] IN { FAT, PPD, PTD, LWC }
            )
        ) + 0
    )

Regards,

Xiaoxin Sheng

[bikram_laishram]

HI Xiaoxin,

Thank you for the reply. I think this might be able to achieve what I want but as of now, it needs some change I think. I have a date table for time intelligence calculations. The 'Incident Crew" and 'Time Hierarchy' are connected using 'Date of Event' to Date. I didn't follow the two variables you have created.

LTI 1 =

VAR selected =

SELECTEDVALUE ('Time Hierarchy'[Date])

VAR curr =

MAX ( 'Incident Crew'[Date_Of_Event] )

RETURN

IF (

curr = selected,

CALCULATE (

[Injury Count],

FILTER (

'Incident Crew',

'Incident Crew'[Severity_Of_Injury_Id] IN { 111664,111665,111666,111667 }

)

) + 0

)