cancel
Showing results 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

Helper I

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

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

3 REPLIES 3
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

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
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors