Forum Discussion
Ranks with filter function date
Hello to all,
I use the following function filter for my Ranks measurement but I can't get the result I want:
Index_Arrets_Turbidity =
VAR min_ =
MIN ( dim_date[Date] )
VAR max_ =
MAX ( dim_date[Date] )
RETURN
RANKX (
FILTER (
ALL ( fact_points_measures_faucon ),
fact_points_measures_faucon [id_measure_faucon] = 370
&& fact_points_measures_faucon [dt (1j)] >= min_
&& fact_points_measurements_faucon [dt (1d)] <= max_
),
CALCULATE (
AVERAGE ( fact_points_measurements_faucon [Index] ),
dim_measurements_faucon [group_measurement] = "Turbidity".
),
,
CSL,
SKIP
)
I would like the values of DATE() not to be filled in manually but to be filled in automatically according to my time segment :
I share my pbix by this link: https://1drv.ms/u/s!Ao1OrcTeY008gYU2OOM2oBSrDBzgpQ?e=1kZwbg
Thanks in advance,
Joël
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
18 Replies
In your FILTER, you are using ALL on the whole fact table which is removing filters from your date table too. Try just putting the columns you are filtering on in the ALL instead ( maybe use ALLSELECTED too).
FILTER (
ALL ( fact_points_measures_faucon[id_measure_faucon], fact_points_measures_faucon[dt (1j)], fact_points_measure_faucon[dt (1d))] ),
fact_points_measures_faucon [id_measure_faucon] = 370
&& fact_points_measures_faucon [dt (1j)] >= min_
&& fact_points_measurements_faucon [dt (1d)] <= max_
),Pat
Hello mahoneypat
Thanks for your help but it doesn't work either, my visual loads without finding any result...
Another idea can be ? I shared my pbix to make it easier to understand.
Thanks in advance,
Joël
Thanks for sharing your pbix. I looked at it but am still a little confused. Please tell me if this measure is closer to what you are looking for. If not, please explain the calculation you are trying to do.
Index_Arrets_Turbidity New =
RANKX (
CALCULATETABLE (
VALUES ( fact_points_mesures_faucon[dt] ),
ALLSELECTED ( fact_points_mesures_faucon[dt] ),
fact_points_mesures_faucon[id_mesure_faucon] = 370
),
CALCULATE (
AVERAGE ( fact_points_mesures_faucon[Index] ),
dim_mesure_faucon[groupement_mesure] = "Turbidité"
),
,
ASC,
SKIP
)Pat
mahoneypat thank you for your help so fast and precious nevertheless I get this result:
Unfortunately this is not the result I want.
What I would like to have is a Ranks function that automatically recalculates itself according to the time segment.
Roughly the same result as this function:But without using "ALLSELECTED" because this function truncates the result of the other measurements that are related to it.
Thanks for your help
Joël
Can you give an example of another calculation that is impacted by the ALLSELECTED on that column?
Pat