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
The use of variables is a good technique to learn, as it makes your code more readable but more importantly it prevents unnecessary recalculation of the same result many times.
DAX: Use variables to improve your formulas - Power BI | Microsoft Docs
The biggest suggestion I have for you is to change how you store the datetime values. Having high granularity columns (columns with many distinct values) quickly increases your file size. The dt column of your fact table has 2 million values and is the biggest contributor to filesize (id is big too). It is best practice to store dates and times in separate columns to reduce file size, and get rid of any columns you don't need. It would also be better to store your rounded time values (dt(10 min)) as times only (without dates), as you have the date already in another column.
FYI that you can use a tool like DAX studio to see what is driving for file size, as well as many other good features. Here is the view of the sizes for your columns.
You should also turn off Auto Date/Time in your model and use a separate Date table in your models.
Creating a simple date table in DAX - SQLBI
Here is a different way to find your 144th value. However, I would suggest you consider a different logic. You could get the last result at the end of the first day, average of last results for all days in scope, etc.
New 144 Value =
VAR Summary =
ADDCOLUMNS (
FILTER (
SUMMARIZE (
fact_points_mesures_faucon,
dim_mesure_faucon[groupement_mesure],
fact_points_mesures_faucon[Index]
),
dim_mesure_faucon[groupement_mesure] = "Turbidité"
),
"cTurbid",
CALCULATE (
AVERAGE ( fact_points_mesures_faucon[valeur] )
)
)
VAR Top144 =
TOPN (
144,
Summary,
fact_points_mesures_faucon[Index], ASC
)
VAR Top1 =
TOPN (
1,
Top144,
fact_points_mesures_faucon[Index], DESC
)
VAR vResult =
MAXX (
top1,
[cTurbid]
)
RETURN
vResult
Regards,
Pat
It's very interesting what you advise me about date management. I'm considering doing it but I'm afraid it's unmanageable given the number of "ID_measures" I have in my document. I don't know how to do it. Is it long? Most of the examples I find on the internet are about product sales. Here, as you can see, my document records values from different sensors and I try to create KPIs with them. I can't find a similar example allowing me to create a date table for this application.
Thanks in advance,
Joël
The use of a date table like in that link can be used in your model, but it is not critical on this one (but keep these points in mind for future model). For the ID column, since you have a distinct value on each row, you can just use Countrows instead in many cases. If you switched to an approach to take the last value of the day, you wouldn't need the index column. Not critical, but you could significantly decrease the size of your model.
Pat
I understand. Thank you for these tips again!
I look forward to hearing from you and thank you again for your help!
See you soon!
Joël