Calculate based on text filter

DAX DAX Calculations
1

I’ve managed to get to the end result I’m looking for in TtlApptHours but my path there seems pretty clunky so wondering if there was a more direct path that I missed along the way.

I need to calculate how may ‘appointment’ hours were served during a week recognizing some appts are 30 minutes (defined within appointment type) and all others are 60 minutes.
I tried a number of ways within 1 dax statement but as a beginner I couldn’t quite get to the right result thus the step by step measures.

Step by step works but I have to believe there’s a more direct path to this same result for TTLApptHours.

EsConceptTest.pbix (290.8 KB)

hourcalc
hourcalc1496×614 34 KB

Thanks in advance

2

Hello @gkavesh,

Thank You for posting your query onto the Forum.

You can use the “Variables Technique” to achieve the results here. Below is the formula provided for the reference where everything is put under the one single measure rather than creating multiple measures.

TTlApptHours - Harsh = 
VAR _Visit_Type_Count = 
COUNT( Operations[Type] )

VAR _Productivity_Appt_Ttls = 
CALCULATE( 
    COUNTX( operations , Operations[Type] ) , 
        FILTER( Operations , 
            CONTAINSSTRING( Operations[Type] , 30 ) = TRUE ) )

VAR _30_Min_Appt = 
IF( 
    _Productivity_Appt_Ttls >= 1 , 
    _Productivity_Appt_Ttls * .5 ,
    0 )

VAR _Results = 
_Visit_Type_Count - _Productivity_Appt_Ttls + _30_Min_Appt

RETURN
_Results

If you observe I’ve put all your formulas into one measure by following the very same logic. And the final results are same. Below is the screenshot of the results provided for the reference -

Final Results
Final Results867×395 18 KB

I’m also attaching the working of the PBIX file for the reference as well as providing a link below about “How To Use Variables In Power BI”.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

EsConceptTest - Harsh.pbix (291.0 KB)

2 Likes
3

Thank you! The how to & video are quite useful and fills in more gaps in my learning.

4

Hello @gkavesh.

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Have a happy weekend.

Thanks and Warm Regards,
Harsh