need help

[Bhavyamalik149]

i've 2 qlik measures which are vaiable beneath layer . Can someone help me in converting them into dax
RTY & FPY Global Average are the final measures in qlik used to create the measures:

RTY = $(M1log(AggrLvl1))

M1log = if(min($(Calc3($1)))=-1,0,Exp(Sum($(Calc3($1)))))
calc3 = Aggr({<$(FPYSet1),$(FPYSet2),$(FPYSet5)>}

               if($(calcCDWON_rwrk)<$(calcCDWON),

               log(1

              -

          $(calcCDWON_rwrk)/$(calcCDWON)

           )

       ,

       -1)

               ,

   $($1)

   )

calcCDWON = Count(DISTINCT {<$(FPYSet1),$(FPYSet2),$(FPYSet5)>} WipOrderNo)

calcCDWON_rwrk = Count(DISTINCT {<$(FPYSet1),$(FPYSet3)>} WipOrderNo)

Where

fpyset1 : operation status = 5

fpyset2 : ReasonCode=

fpyset3 : isreworkflag = 1

fpyset5 : ReasonCodeGroup=

aggrlvl1 = workstation

reasoncode, reasoncodegroup isreworkflag, workstation, operation status and wiporder no are columns from fact table

Qlik Formula FPY Global Average:

calc1_global = Avg($(FPYSetExpr1)

Aggr($(FPYSetExpr1)

1 - $(calcCDWON_rwrk_global)/$(calcCDWON_global), $(AggrLvl1)))

where

calcCDWON_global = Count(DISTINCT {1<$(FPYSet2),$(FPYSet5)>} WipOrderNo)

calcCDWON_rwrk_global = Count(DISTINCT {1<$(FPYSet3)>} WipOrderNo)

Where

FPYSetExpr1 = {1<Year=P({1}Year)>}

fpyset1 : operation status = 5

fpyset2 : ReasonCode=

fpyset3 : isreworkflag = 1

fpset 5: ReasonCodeGroup=

aggrlvl1 = workstation

reasoncode, reasoncodegroup, isreworkflag, workstation, wiporder no, operation status are columns from fact table

I've been struggling with them since a long time, if someone can help, that would be really helpful. thanks 🙂

[v-pnaroju-msft]

Hi Bhavyamalik149,

Kindly provide some sample data which explains your problem or question clearly in a simple and neat format, not as an image. This will help us to understand and solve the issue more effectively. Please ensure the data is relevant, does not have any sensitive details, and is connected to your problem. Also, let us know what result you expect from this sample.

Thank you.

[v-pnaroju-msft]

Hi Bhavyamalik149,

Please share some sample data that shows your problem or question clearly in a simple and organized way (not as an image). This will help us understand and solve the issue better. Make sure the data is relevant, does not have any sensitive information, and is related to your problem. Also, please tell us what result you expect from this example.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi Bhavyamalik149,

Thank you for the update.

We kindly request you to provide sample data that clearly demonstrates your issue in a structured format (not as an image) to help us understand and resolve the matter. Please ensure that the data is relevant, free from any sensitive information, and directly related to the issue. Additionally, please share the expected outcome based on the given example.

Thank you.

[v-pnaroju-msft]

Thankyou, @bhanu_gautam, for your response.

Hi Bhavyamalik149,

We appreciate your question on the Microsoft Fabric Community Forum.

Based on my understanding of the scenario, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter:

We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Bhavyamalik149]

thanks, this was helpful but when i'm drilling it down the to further level, it is still not exact but still a lot better. thanks 🙂

[bhanu_gautam]

@Bhavyamalik149 , Try using

-- RTY = EXP(SUMX(VALUES([Workstation]), [Calc3]))
-- [Calc3] = IF([calcCDWON_rwrk] < [calcCDWON], LOG(1 - DIVIDE([calcCDWON_rwrk], [calcCDWON])), -1)

RTY :=
VAR Workstations = VALUES(Fact[Workstation])
VAR Calc3Table =
ADDCOLUMNS(
Workstations,
"calcCDWON", CALCULATE(
DISTINCTCOUNT(Fact[WipOrderNo]),
Fact[OperationStatus] = 5,
Fact[ReasonCode] = BLANK(),
Fact[ReasonCodeGroup] = BLANK()
),
"calcCDWON_rwrk", CALCULATE(
DISTINCTCOUNT(Fact[WipOrderNo]),
Fact[OperationStatus] = 5,
Fact[IsReworkFlag] = 1,
Fact[ReasonCode] = BLANK()
)
)
VAR Calc3 =
ADDCOLUMNS(
Calc3Table,
"Calc3Value",
IF(
[calcCDWON_rwrk] < [calcCDWON],
LOG(1 - DIVIDE([calcCDWON_rwrk], [calcCDWON])),
-1
)
)
VAR SumCalc3 = SUMX(Calc3, [Calc3Value])
RETURN
IF(MINX(Calc3, [Calc3Value]) = -1, 0, EXP(SumCalc3))

And 

-- FPY Global Average = AVERAGEX(VALUES([Workstation]), 1 - DIVIDE([calcCDWON_rwrk_global], [calcCDWON_global]))

FPY_Global_Avg :=
VAR Workstations = VALUES(Fact[Workstation])
RETURN
AVERAGEX(
Workstations,
VAR calcCDWON_global = CALCULATE(
DISTINCTCOUNT(Fact[WipOrderNo]),
Fact[ReasonCode] = BLANK(),
Fact[ReasonCodeGroup] = BLANK()
)
VAR calcCDWON_rwrk_global = CALCULATE(
DISTINCTCOUNT(Fact[WipOrderNo]),
Fact[IsReworkFlag] = 1
)
RETURN
1 - DIVIDE(calcCDWON_rwrk_global, calcCDWON_global)
)

[Bhavyamalik149]

Thanks Bhanu for being helpful but it did not get the desired result, infact it was too far fetched than it was expected.