Forum Discussion
Ranks with filter function date
The problem was that your Val_Turb_Freq_1h measure was not returning values for the day level rows. Here is a new measure that seems to work. It's a little off as your hardcoded values use a value of 81 as the threshold but the overall result for Val_Turb_Freq_1h is 80. The result from this measure matches those when 80 is used.
NewMeasure =
VAR ValTurbFreq1h =
CALCULATE (
[Val_Turb_Freq_1h],
ALLSELECTED ()
)
VAR vStop =
COUNTROWS (
FILTER (
VALUES ( fact_points_mesures_faucon[dt (10 min)] ),
[Turbidité] >= ValTurbFreq1h
)
) / 6
RETURN
vStopAlso, the logic to get your threshold values seems to be the 144th value of Turbidity (regardless if the scope is day, month, year or all time). Is that your intended logic?
Pat
I spent some time looking at it and am still a little confused at the calculation you are trying to do (the green tables aren't in the model). I'm sure there is a simpler way to calculate this. Can you explain in one sentence what you are looking for? In any case, one suggestion is to calculate [Val_Turb_Freq_1h] as a variable up front.
Arrêts turbidité_temps_1j (en h) =
var ValTurbFreq1h = [Val_Turb_Freq_1h]
Return
SUMX(SUMMARIZE(VALUES(fact_points_mesures_faucon[dt (10 min)]),fact_points_mesures_faucon[dt (10 min)],"STOP",IF([Turbidité]>=ValTurbFreq1h,1,0)/6),[STOP])
Pat
Thank you again for your time mahoneypat . I have updated the PBIX link by adding the two green boards. I tested the formula but unfortunately it doesn't work ...
https://1drv.ms/u/s!Ao1OrcTeY008gYU2OOM2oBSrDBzgpQ?e=NBEX1B
Joël
Thank you. I'll take a look this evening after work (but others should feel free to chime in to help too). Can you also provide a one-sentence summary of the desired calculation? I may start from scratch (the current approach isn't performant even when it gets fixed).
Pat
Thank you mahoneypat, I will try to explain what I want to calculate. I've added a visual in the link of my PBIX to make it more understandable. I would like the green table on the left to calculate for each day the shutdown duration according to the turbidity threshold that is defined upstream.In fact, I would like the same results as the green table on the right but using the value of the measurement: "Val_turb_freq_1h" instead of "80" in the IF measurement "Stop_turbidity_Freq_1h = IF([Turbidity]>=80, 1.0)" so that the calculation is done automatically without me entering the value manually.
https://1drv.ms/u/s!Ao1OrcTeY008gYU2OOM2oBSrDBzgpQ?e=jz8hV5
Thank you for your time,
JoëlThe problem was that your Val_Turb_Freq_1h measure was not returning values for the day level rows. Here is a new measure that seems to work. It's a little off as your hardcoded values use a value of 81 as the threshold but the overall result for Val_Turb_Freq_1h is 80. The result from this measure matches those when 80 is used.
NewMeasure =
VAR ValTurbFreq1h =
CALCULATE (
[Val_Turb_Freq_1h],
ALLSELECTED ()
)
VAR vStop =
COUNTROWS (
FILTER (
VALUES ( fact_points_mesures_faucon[dt (10 min)] ),
[Turbidité] >= ValTurbFreq1h
)
) / 6
RETURN
vStopAlso, the logic to get your threshold values seems to be the 144th value of Turbidity (regardless if the scope is day, month, year or all time). Is that your intended logic?
Pat