Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors