topic Re: CALCULATE function giving incorrect result when used with a Measure filter condition in DAX Commands and Tips

CALCULATE function giving incorrect result when used with a Measure filter condition

narsimha — Tue, 21 Feb 2023 02:01:21 GMT

I have a dataset that has ticket numbers with multiple iterations for each ticket and each iteration has multiple stages.

Time duration in minutes is captured at each stage and each iteration.

I have a business rule saying if the ticket is stuck at any iteration for more than 24 hours (Including all stages) then its flagged as 'Non Compliance' and under 24 hours then 'Complaince'.

So in the below example ticket , there are 8 iterations and I have calculated the total number of minutes at each iteration using the measure SLA Minutes.

Created another measure SLA Result to check if that iteration is in Compliance or Non Complaince.

Iterations = Count of distinct Iterations

SLA minutes = CALCULATE(SUM(wwlp_statustimerecorder[StatusMinutes]))

SLA Result = IF([SLA minutes ]<=1440,"Complaince","Non Complaince")

Now I need to calculate the % of complaince SLA which is Total # of iterations with complaince SLA divided by Total # of iterations.

for this I first created the below Measure ComplianceSLA to get the # of Complaince SLA iterations.

ComplianceSLA = CALCULATE(CALCULATE([Iterations]),FILTER(wwlp_statustimerecorder,[SLA Result]="Complaince"))

Initially tried without FILTER function in the CALCULATE function

As we can see there are 8 iterations for this ticket # and 2 are in compliance rest 6 are Non Compliance.

But the ComplianceSLA measure is giving me wrong answer as 7 instead of 2.

Could you please advice me where am I doing wrong and how to get the correct result.

Thanks

Re: CALCULATE function giving incorrect result when used with a Measure filter condition

BrianConnelly — Tue, 21 Feb 2023 04:33:32 GMT

Is [SLA Result] a calculated column or a measure?

If it is a measure, then you need to work with Summarize functions. Let me know and I can help you here, but need more details in what you created.

Re: CALCULATE function giving incorrect result when used with a Measure filter condition

narsimha — Tue, 21 Feb 2023 05:33:10 GMT

Yes SLA Result is a Measure.

Could you please Let me know how i can achive this? also let me know what details you need.

Re: CALCULATE function giving incorrect result when used with a Measure filter condition

BrianConnelly — Mon, 27 Feb 2023 20:48:59 GMT

Should be something like the following...

Measure = VAR TotalIterations = SUMX(Table, [Iterations]) VAR TotalIterationsNonCompliance = SUMX(SUMMARIZE(Table,TicketNumber,"NewMeasure",COUNTAX(Filter(Table, Minutes>=1400),Ticketnumber),[NewMeasure]) RETURN DIVIDE(TotalIterationsNonCompliance ,TotalIterations ,0)