topic Filter calculates shifts the time in DAX Commands and Tips

Filter calculates shifts the time

Anonymous — Wed, 25 Aug 2021 13:52:05 GMT

Hello to all,

Here is a report of production monitoring of a hydraulic power plant. My problem concerns the graph below which corresponds to a flow monitoring sensor. My need : I would like to display the same values in the graph whose title is "Débits vigicrues (m3/s)" but with the values advanced by 4 hours. Example : The data in the tooltip whose value is "29" should appear on 18/08/2021 12:20:00 and not on 18/08/2021 08:20:00. I could advance 4 hours all my datetime column but in my case, the problem concerns only this flow sensor. The other sensors in my report are on the right time zone.

Here is the code of the flow measurement:

Q (m3/s) = CALCULATE(AVERAGE(fact_points_mesures_faucon[valeur])/1000,dim_mesure_faucon[groupement_mesure]="Debit_Vigicrue")

As well as the architecture of the data table:

Would you have a solution for me please?

Thank you in advance for your help,
Have a nice day,

Joël

Re: Filter calculates shifts the time

Anonymous — Wed, 25 Aug 2021 14:14:24 GMT

Just create a custom tooltip...

Re: Filter calculates shifts the time

AlB — Wed, 25 Aug 2021 14:16:10 GMT

Hi Anonymous

How about creating a new column in which you add the four hours only for that sensor and leave the time unchanged for the others? You could also do this in Power query, which would probably be best.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Filter calculates shifts the time

Anonymous — Wed, 25 Aug 2021 15:13:18 GMT

Hello AlB and Anonymous and thanks for your help,

Let me explain my problem in more detail.
In the graph framed in red below, you find the measurement of the realized power (green line) whose data are time-stamped at the right time zone.
The blue dotted line corresponds to the theoretical production according to a mathematical model.

Below the measurement of the mathematical model:

"P_théorique_H07 (kW) =
VAR _P =
IF(
AND(12<=[Q (m3/s)],[Q (m3/s)]<21),
12.33271181*[Q (m3/s)]^2+(-426.5508019)*[Q (m3/s)]+4287.235691
)
+
IF(
AND(23<=[Q (m3/s)],[Q (m3/s)]<32),
3.391495147*[Q (m3/s)]^2+(-162.8664303)*[Q (m3/s)]+2672.165273
)
+
IF(
AND(34<=[Q (m3/s)],[Q (m3/s)]<43),
-2.186881051*[Q (m3/s)]^2+184.5489415*[Q (m3/s)]+(-2870.04308)
)
+
IF(
43<=[Q (m3/s)],
0.001673895*[Q (m3/s)]^2+(-2.044218339)*[Q (m3/s)]+1098.267649
)
return
IF([Q (m3/s)]<12,0,_P)"

As you can see I am using the measurement "Q (m3/s)" To create my model:

"Q (m3/s) = CALCULATE(AVERAGE(fact_points_mesures_faucon[valeur])/1000,dim_mesure_faucon[groupement_mesure]="Debit_Vigicrue")"

The theoretical mathematical model measurement is working fine but it is off by 4h with the realized production line as you can see on the graph.
I would like them to be aligned by changing the timeline of the "Q (m3/s)" measure.

Is it clearer?

Thanks in advance

Joël

Re: Filter calculates shifts the time

Anonymous — Wed, 25 Aug 2021 17:17:49 GMT

OK. First, this is what I'd write as a mathematician 🙂

P_théorique_H07 (kW) = var Q_Bounds = DATATABLE( "'Q_Bounds'[LowerBound]", integer, "'Q_Bounds'[UpperBound]", integer, "'Q_Bounds'[A]", double, "'Q_Bounds'[B]", double, "'Q_Bounds'[C]", double, { {12, 21, 12.33271181, -426.5508019, 4287.235691}, {23, 32, 3.391495147, -162.8664303, 2672.165273}, {34, 43, -2.186881051, -2.044218339, 1098.267649}, // 2^53-1 = 9007199254740991 -- max 64-bit int for DAX {34, 9007199254740991, 0.001673895, -2.044218339, 1098.267649} } ) var Q_ = [Q (m3/s)] var P_ = MAXX( filter( // This filter will return at most 1 row // if the intervals defined by the bounds // are of the form [a, b) and are disjoint. Q_Bounds, 'Q_Bounds'[LowerBound] <= Q_ && Q_ < 'Q_Bounds'[UpperBound] ), var A_ = 'Q_Bounds'[A] var B_ = 'Q_Bounds'[B] var C_ = 'Q_Bounds'[C] return A_ * (Q_)^2 + B_ * (Q_) + C_ ) return P_

And I'll tell you exactly why you should write measures this way. It's because now it'll be easy to deal with your issue: you'll have to change this code in one place only, namely, where you calculate your Q_ variable.

Now, instead of [Q (m3/s)] in the above formula, you should use this modified version:

[Q Shifted (m3/s)] = var ShiftInHours = 4 var ShiftDirection = -1 -- or +1, you have to decide var CurrentTime = MAX( YourTimeAxisTable[Time] ) var ShiftedTime = CurrentTime + ShiftDirection * TIME( ShiftInHours, 0, 0 ) var Result = CALCULATE( [Q (m3/s)], YourTimeAxisTable[Time] <= ShiftedTime, ALL( YourTimeAxisTable ) ) return Result

Choose the direction of the shift.

And I'll tell you even more. The table built in the first formula I'd move into a base table in the model so that it could be loaded from some source. This way your model would be truly dynamic. You'd be able to load it from some store, say SQL table. This would be the ultimate in flexibility.

Re: Filter calculates shifts the time

Anonymous — Thu, 26 Aug 2021 08:24:00 GMT

Wow Anonymous
First of all thank you for this impressive work, it's really very generous of you.

Nevertheless, after doing the test I still have a similar result as can be seen below with the dotted line "P_New_théorique_H07 (kW)".

Maybe there is a problem with my time table. I'm sharing my Pbix via OneDrive, could you please take a look at it?
https://1drv.ms/u/s!Ao1OrcTeY008gYVc1kaZn5_ZUakonQ?e=xJXjpz

Thanks in advance,

Joël

Translated with www.DeepL.com/Translator (free version)

Re: Filter calculates shifts the time

Anonymous — Thu, 26 Aug 2021 08:41:21 GMT

Hi Anonymous

Yes, it may very well be that your time axis is not what I've assumed it to be. It may even be that my mental picture of your model is not right. I'm at work right now, hence I can't look at your file since I can't download it due to the security policies. I'll have a look at this when I'm back home. I'm sure there's just a little bit of tweaking and it'll work.

Re: Filter calculates shifts the time

Anonymous — Thu, 26 Aug 2021 08:43:06 GMT

Anonymous That's nice, thank you very much for your time

Re: Filter calculates shifts the time

Anonymous — Thu, 02 Sep 2021 15:26:49 GMT

Hello AlB

I would like to ask you again about my problem because you seemed to have understood it well. Were you able to find something interesting?

Thank you in advance,

Joël