Forum Discussion

caio-pavesi's avatar
caio-pavesi
Frequent Visitor
2 months ago
Solved

How to make a DAX calculation return BLANK rows

I'm working on a Power Bi template for my company. Since we are a global comapny i added Time Zone tables that help the end-user filter the visual for their Time Zone.

Now I'm trying to create a DAX measure that will get the values in a DATE/TIME type column and sum the given UTC Offset in Hours, however I'm having a hard time when there's no value in a give row, DAX will just either not return the rows with blank or return the offset.

 

 

What I'm trying to achieve is a function that will ignore the blank values and still return them, and when there is a value actually SUM the End Time UTC with the Offset.

 

Note: [Current UTC Offset] is a measure that returns a decimal number from -12 to +12

 

How can i achieve this?

"DAX help"
"DAX"
"Need Help"
"Time Intelligence"
  • jgeddes's avatar
    jgeddes
    2 months ago

    If I understand correctly you want blank rows returned when the End Time UTC value is blank. Something like...

    Two steps are needed for this result. 
    First I created the measure...

    End Time Adjusted = 
SUMX(
    'Table',
    IF(ISBLANK([End Time UTC]), BLANK(), [End Time UTC] + [Current UTC Offset])
)

    Once that measure is added to the table you need to right click on the Approval Id field and select 'Show items with no data'.

     


    Hope this helps.

2 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 months ago

    If I understand correctly you want blank rows returned when the End Time UTC value is blank. Something like...

    Two steps are needed for this result. 
    First I created the measure...

    End Time Adjusted = 
SUMX(
    'Table',
    IF(ISBLANK([End Time UTC]), BLANK(), [End Time UTC] + [Current UTC Offset])
)

    Once that measure is added to the table you need to right click on the Approval Id field and select 'Show items with no data'.

     


    Hope this helps.