DAX Expression logic | SSAS Tabular model

Question

Hi Team,

In our project , we were using SSAS tabular model where we have 2 objects.
1> Dim Employee
2> Fact Employee Utilisation Details

We have only 1 matching record based on the emp_sk key column.
we need to calculate an additional measure called Billable Utilisation based on the below logic.
DAX expression used:
Billable Utilisation :=
IF([Daily Hours]-[Holiday Hours] <=0,0,
DIVIDE([Billable Hours],[Daily Hours]-[Holiday Hours],0))

The functionality is working fine as expected and getting only 1 record when we dont browse Billable Utilisation measure
but getting all the records when we try to browse Billable Utilisation measure.
Can you please help me, if any change need to be done for my DAx expression.

Thanks in advance
Santosh S

Answer 1

Replace the zero with blank in the first branch of the IF, and just drop it from the DIVIDE.. Zero is a meaningful value for the engine and is thus shown, while rows with all blank values are normally suppressed.

Answer 2

Hi @Santosh Kumar ，
Try：

Billable Utilisation :=  
IF([Daily Hours]-[Holiday Hours] <0,BLANK (),  
DIVIDE([Billable Hours],[Daily Hours]-[Holiday Hours],BLANK ()))  

For measures, it's usually a better design that they return BLANK. Returning BLANK is better because report visuals—by default—eliminate groupings when summarizations are BLANK. It allows the visual to focus attention on groups where data exists.

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.