Skip to main content
cancel
Showing results for 
Search instead 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

Reply

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.

2019-05-10 12_49_09-Injury Analysis - Excel.png

 

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
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
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.

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
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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