Forum Discussion
need help
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 🙂
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.
7 Replies
Anonymous , 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)
)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.
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.
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.
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.
