Converting a QlikSense expression into a Power BI measure

[ManchevB]

Hello everyone, 

I am working on creating a Power BI report that will visualize engineer activity - amount of work orders they had, travel time, repair time and compairson with their peer group. 

What I am struggling to do is how to calculate the utilization of the engineers and what I need to do is replicate this QlikSense expression into Power BI measure: 

QlikSense expression:  Sum({<WO_Type_Name={'Repair', 'Installation', 'Proactive', 'Onsite T&E case', 'Genesis'}, IsPI_WO={1}, [WO Total Time In Hours]={"<=14"}>} [WO Total Time In Hours])/
Sum(Aggr(Count({<WO_Type_Name={'Repair', 'Installation', 'Proactive', 'Onsite T&E case', 'Genesis'}, IsPI_WO={1}, [WO Total Time In Hours]={"<=14"} >} DISTINCT Onsite_Date_WO), Onsite_Month_WO, [Engineer Badge Id]))/
Avg(Aggr({<WO_Type_Name={'Repair', 'Installation', 'Proactive', 'Onsite T&E case', 'Genesis'}, IsPI_WO={1}, [WO Total Time In Hours]={"<=14"} >} SUM(STD), Onsite_Month_WO, [Engineer Badge Id]))

In QlikSense it appears like so: 

To put that into Power BI I decided to break the whole expression into separate measures:

- I have created me measures for distinct count of engineer badge id, count of work order type name, ISPI=1, measure for work orders that are up to 14hrs of handling, count of onsite date average per onsite month, count of onsite date average per engineer badge id. 

I used Copilot where I was able to understand the logic behind the whole expression and I expressed the measures like so:

1. First Expression  = [Count of Work Order Type Name]+[ISPI_WO]+[Total Time in Hours equal or less than 14]

2. Second Expression  = [Count of Work Order Type Name]+[ISPI_WO]+[Total Time in Hours equal or less than 14]+[2. Count of Onsite_Date_WO average per Onsite_Month_WO]+[Count of Onsite_Date_WO average per Engineer_Badge_Id]

3. Final Expression that should be equal as the whole QlikSense expression: 

[1. First Expression 10.05.2024]/[2. Second Expression 10.05.2024]

I cannot understand where the problem comes from, when I try to do a reference check in QlikSense an engineer is utilized in certain percent and in my Power BI it shows completely different. 

Any suggestions will be greatly appreciated.

Happy to share more details if needed. 

Thank you for your time and attention,

Boris

[ManchevB]

Sorry, somehow missclicked and accepted this as solution but the case still remains unsolved*

[v-tianyich-msft]

Hi @ManchevB ,

I don't know much about QlikSense, I hope the following expression will help you:

VAR TotalTimeInHours = 
CALCULATE(
    SUM('Table'[WO Total Time In Hours]),
    'Table'[WO_Type_Name] IN {"Repair", "Installation", "Proactive", "Onsite T&E case", "Genesis"},
    'Table'[IsPI_WO] = 1,
    'Table'[WO Total Time In Hours] <= 14
)

VAR CountDistinctDates = 
CALCULATE(
    DISTINCTCOUNT('Table'[Onsite_Date_WO]),
    'Table'[WO_Type_Name] IN {"Repair", "Installation", "Proactive", "Onsite T&E case", "Genesis"},
    'Table'[IsPI_WO] = 1,
    'Table'[WO Total Time In Hours] <= 14,
    ALLEXCEPT('Table', 'Table'[Onsite_Month_WO], 'Table'[Engineer Badge Id])
)

VAR AverageSTD = 
CALCULATE(
    AVERAGE('Table'[STD]),
    'Table'[WO_Type_Name] IN {"Repair", "Installation", "Proactive", "Onsite T&E case", "Genesis"},
    'Table'[IsPI_WO] = 1,
    'Table'[WO Total Time In Hours] <= 14,
    ALLEXCEPT('Table', 'Table'[Onsite_Month_WO], 'Table'[Engineer Badge Id])
)

RETURN
DIVIDE(
    TotalTimeInHours,
    CountDistinctDates,
    BLANK()
) 

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ManchevB]

Hey, thank you for this effort!

I tried using chat GPT to convert the QlikSense formula to DAX in numerous ways, but still the result is not what I have in QlikSense: I am trying to replicate the utilization and get the exact same numbers.. .

[ManchevB]

Here is a link to a sample report where the data sits: Sample Report  , for the outcome, I can only share a snapshot of the percents I am looking to get: 

the example I am using is Engineer badge id: 50000660

and on Onsite Month: 

Unfortunatelly I am not able to send extract of the data from QlikSense, I only have the expression that calculates the utilization: Sum({<WO_Type_Name={'Repair', 'Installation', 'Proactive', 'Onsite T&E case', 'Genesis'}, IsPI_WO={1}, [WO Total Time In Hours]={"<=14"}>} [WO Total Time In Hours])/
Sum(Aggr(Count({<WO_Type_Name={'Repair', 'Installation', 'Proactive', 'Onsite T&E case', 'Genesis'}, IsPI_WO={1}, [WO Total Time In Hours]={"<=14"} >} DISTINCT Onsite_Date_WO), Onsite_Month_WO, [Engineer Badge Id]))/
Avg(Aggr({<WO_Type_Name={'Repair', 'Installation', 'Proactive', 'Onsite T&E case', 'Genesis'}, IsPI_WO={1}, [WO Total Time In Hours]={"<=14"} >} SUM(STD), Onsite_Month_WO, [Engineer Badge Id]))

 this is the one I need to convert to a Power BI DAX formula. 

I remain available if more clarification is needed. 

[Ritaf1983]

Hi @ManchevB 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523