topic Re: Column in datatable with dynamic function paramater in DAX Commands and Tips

Column in datatable with dynamic function paramater

Atiroocky — Tue, 22 Aug 2023 07:29:24 GMT

Hello,

I managed to calculate a cumulative rolling day in a column of my table.

I use the function "DATESINPERIOD"

I would like to replace the "static" number parameter by a dynamic parameter

I know that datatable are kind of "static" and can’t be updated with a "what-if" paramater.
By the past I managed to generate a dynamic datable thanks to "SWITCH" function, but it was totally different needs and I can’t apply this "trick" to my actual need.

Any help would be appreciated !

Thanks

Re: Column in datatable with dynamic function paramater

tamerj1 — Tue, 22 Aug 2023 08:39:01 GMT

Hi Atiroocky
Calculated columns cannot be dynamic as they do not interact with the fiter conext of the report. This has to be a measure

Count = CALCULATE ( SUM ( 'Table_DHU'[CityLocation] ), ALL ( 'Table_DHU' ), DATESINPERIOD ( 'Table_DHU'[Date], MAX ( 'Table_DHU'[Date] ), - [Parameter], DAY ) )

Re: Column in datatable with dynamic function paramater

Atiroocky — Tue, 22 Aug 2023 09:02:09 GMT

Thanks, that works perfectly !

As a "beginner" I’m not very comfortable playing with "Measures". A bit earlier before posting in this forum, I tried to put the column code in a measure, but I used Table_DHU[Date] as start date instead of "MAX(Table_DHU[Date])". I was returning me an error.
For me "MAX" was the last entry of all table data. But I was thinking as "table" data and no "measure" data. It is still a bit confused in my mind but time will help.

Thanks again !

Re: Column in datatable with dynamic function paramater

tamerj1 — Tue, 22 Aug 2023 09:07:32 GMT

Atiroocky
MAX ( 'Table'[Column] ) when used in a calculated column returns the max value in this column becuase there is no filter context. However when used in a measure it will return the max value avialable in the current filter context. In other words, the value is different from one row to another.

Re: Column in datatable with dynamic function paramater

Atiroocky — Tue, 22 Aug 2023 09:10:00 GMT

Thanks for your explanation. That helps me better understanding.