topic Re: undefined in DAX Commands and Tips

undefined

AditiGupta_ — Thu, 28 May 2026 02:51:47 GMT

Hi,

I need to find out the solution that even if we have data of output hours for given entities but if input hours are not available for some specific entities then prod efficiency should show 0 for those specific entities. Even after selecting multiple entities at same time. It should only reflect or calculate when we have both input and output hours, so that it will calculate accordingly.

Re: undefined

Natarajan_M — Thu, 28 May 2026 12:05:10 GMT

Hi AditiGupta_ , Can you explain the problem statement with a sample data for better understanding ?

Thanks

Re: undefined

AditiGupta_ — Thu, 28 May 2026 14:40:51 GMT

Hi,

I have added screenshot please have a look, and let me know asap!!

Thank you for helping!!

Re: undefined

oussamahaimoud — Thu, 28 May 2026 16:34:48 GMT

Hi AditiGupta_ ,

Try to update DAX measures by :

1. Output Hours (Final): add a conditional check

Output Hours (Final) =

VAR _InputHrs = CALCULATE(SUM('Input Hrs'[Input Hours]))

VAR _OutputHrs = SUM('Warehouse PRODROUTETRANS'[Output Hours])

RETURN

IF(

NOT ISBLANK(_InputHrs) && _InputHrs > 0,

_OutputHrs,

BLANK()

)

2. Input Hours (Final): stays clean

Input Hours (Final) =

VAR _InputHrs = CALCULATE(SUM('Input Hrs'[Input Hours]))

RETURN

IF(

NOT ISBLANK(_InputHrs) && _InputHrs > 0,

_InputHrs,

BLANK()

)

3. Prod Eff: the critical fix

Prod Eff =

VAR _InputHrs = [Input Hours (Final)]

VAR _OutputHrs = [Output Hours (Final)]

RETURN

IF(

ISBLANK(_InputHrs) || _InputHrs = 0,

0, -- No input hours → show 0

DIVIDE(_OutputHrs, _InputHrs, 0)

)

4. Total Output Hours for KPI Card (only counts valid entities)

Total Output Hours (KPI) =

SUMX(

VALUES('Warehouse PRODROUTETRANS'[Entity]), -- loop per entity

VAR _InputHrs = CALCULATE(SUM('Input Hrs'[Input Hours]))

VAR _OutputHrs = CALCULATE(SUM('Warehouse PRODROUTETRANS'[Output Hours]))

RETURN

IF(

NOT ISBLANK(_InputHrs) && _InputHrs > 0,

_OutputHrs,

BLANK() -- excludes entity with no input hours from total

)

Hope this helps! Please, don't forget to accept as solution ✅ and thumbs up 👍 in order to keep helping others.

Best regards,

Oussama (Data Consultant - Expert Fabric & Power BI)

Re: undefined

mizan2390 — Fri, 29 May 2026 14:04:23 GMT

hi AditiGupta_

If you just need the specific entities to display 0 in a table or matrix when they lack input hours, a simple IF statement checking for blanks will do the job.

Prod Efficiency = 
VAR _InputHours = [Input Hours]
VAR _OutputHours = [Output Hours]

RETURN
IF (
ISBLANK ( _InputHours ) || _InputHours == 0,
0, -- Forces a 0 if input hours are missing or exactly zero
DIVIDE ( _OutputHours, _InputHours, 0 )
)

While this fixes the individual entity rows, your Grand Total might still sum up the output of the "zeroed" entities and divide it by the total inputs, which isn't always mathematically accurate for efficiency.

Approach 2:

Because you mentioned selecting multiple entities at the same time, we need to ensure the Grand Total ignores the output hours of any entity that is missing its input hours. Otherwise, your total efficiency is inaccurate.

We can achieve this by using an iterator (FILTER and VALUES) to create a virtual table of only the valid entities before doing the math.

Prod Efficiency = 
-- 1. Build a virtual table of entities in the current filter context that ACTUALLY have input hours
VAR _ValidEntities =
FILTER (
VALUES ( 'YourEntityTable'[EntityName] ), 
NOT ISBLANK ( [Input Hours] ) && [Input Hours] > 0
)

-- 2. Calculate Output and Input strictly for those valid entities
VAR _ValidOutput = 
CALCULATE ( [Output Hours], _ValidEntities )

VAR _ValidInput = 
CALCULATE ( [Input Hours], _ValidEntities )

VAR _CurrentInput = [Input Hours]

RETURN
-- 3. If the current row has no input, return 0. Otherwise, safely divide the valid numbers.
IF (
ISBLANK ( _CurrentInput ) || _CurrentInput == 0,
0,
DIVIDE ( _ValidOutput, _ValidInput, 0 )
)

If this solves your problem, mark this as solution and give me a kudos

Re: undefined

v-sgandrathi — Fri, 05 Jun 2026 04:52:53 GMT

Hi AditiGupta_,

Thank you mizan2390 oussamahaimoud and Natarajan_M for your response to the query.

Does the issue occur only at the individual entity level, or is the problem mainly with the Grand Total/KPI when multiple entities are selected? If possible, please share a small sample of the Input Hours and Output Hours data along with the current DAX measure. This will help determine whether a simple blank check is sufficient or whether the calculation needs to exclude entities without Input Hours from the total aggregation.

Thank you.

Re: undefined

v-sgandrathi — Mon, 08 Jun 2026 06:42:32 GMT

Hi AditiGupta_,

Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.

If you have any further questions, feel free to reach out anytime.

Thank you.