Today() in a measure

[Frajzerr]

Hello,

I have one fact table with about 1,2M rows and a few dim tables, basic star schema. In powerBi canvas I created simple table including a few columns from dim tables and a measure counting rows number in the fact table.

And I found strange behaviour within different measure :

Measure:=

DATEDIFF(

SELECTEDVALUE(

'dimtable'[date]

),

TODAY(),

DAY

)

I don't know why but using TODAY() makes visual performance dramatically slow. For an example I used only TODAY() as a measure and I received an error with lack of memory , I checked it again but I reduced numbers of columns in visual and then it worked but It gave me results of all possible connections between data from dim tables from which I took columns. 

I am trying to undestand this behavour as it is strange to me, and the same for some static value:

ExampleMeasure:=

5

Adding above measure to any table visual even with really small dataset is extemely slow... why ?

But my final question is how can I add today as part of my measure to make it work efficiently.

Thanks in advance !

[Frajzerr]

Unfortunately both solutions don't work 😕

I am sure that today() causing it cause below measure works immediately

Measure:=

MAX('dimtable'[date])

but

Measure:=
Var  T = TODAY()
Return

DATEDIFF(

MAX('dimtable'[date]),

 T ,

DAY

)

engine is not even able to calculate it... Instead of today() I tried even DATE(2022,11,09) and still the same. It doesn't matter whether these function are closed in variables or not. 

[VahidDM]

Hi @Frajzerr 

Try to keep TODAY() as a variable and test that measure performance again:
I'm not sure about your model and visual but you can change the SELECTEDVALUE to MAX

Measure:=
Var _T = TODAY()
Return

DATEDIFF(

MAX('dimtable'[date]),

 _T ,

DAY

)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[amitchandak]

@Frajzerr , Try like 

Measure:=

DATEDIFF(
maxx(allselected('dimtable'), 'dimtable'[date])
TODAY(),
DAY

)