topic Re: Calculate function and direct in DAX Commands and Tips

Calculate function and direct

Anonymous — Wed, 06 Jul 2022 12:58:27 GMT

Hi all,

I'm trying to recreate a report. Right now I've made it with imported data from SQL but the new report is set to use Direct Query.

Previously I requested a solution for this issue: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Retrieving-and-spreading-data-from-many-side-of-a-one-to-may/m-p/2608522#M75574

The solution:

Earleist Start = CALCULATE( MIN('Table2'[Timestamp]), 'Table2'[Timestamp Code] ="Start", ALLEXCEPT('Table1', 'Table1'[Prod No]) ) Latest End = CALCULATE( MAX('Table2'[Timestamp]), 'Table2'[Timestamp Code] ="End", ALLEXCEPT('Table1','Table1'[Prod No]) )

But the formulas don't work when working with Direct Query.

Any ideas on how to solve the issues of not being able to use CALCULATE() and ALLEXCEPT() when using Direct Query?

Thanks!

Re: Calculate function and direct

tamerj1 — Wed, 06 Jul 2022 13:12:03 GMT

Hi Anonymous
Plrease try

Earleist Start = CALCULATE ( MIN ( 'Table2'[Timestamp] ), 'Table2'[Timestamp Code] = "Start", REMOVEFILTERS ( 'Table1' ), VALUES ( 'Table1'[Prod No] ) )

Re: Calculate function and direct

Anonymous — Wed, 06 Jul 2022 13:46:06 GMT

Hi tamerj1 ,

Thansk for your response!

Unfortunately I'm not able to use the CALCULATE function when using Direct Query.

I get this error: "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery model".

Hence the issue as the first solution worked with imported data.

Re: Calculate function and direct

tamerj1 — Wed, 06 Jul 2022 14:05:19 GMT

Anonymous

Please try

Earleist Start = MINX ( FILTER ( ALL ( 'Table2' ), 'Table2'[Timestamp Code] = "Start" && 'Table2'[Prod No] = SELECTEDVALUE ( 'Table2'[Prod No] ) ), 'Table2'[Timestamp] )

Re: Calculate function and direct

Anonymous — Thu, 07 Jul 2022 06:38:42 GMT

Hi tamerj1

The MINX function is not supported either.

Unfortunately I still seem to be stuck.

Thanks for your help!

Re: Calculate function and direct

tamerj1 — Thu, 07 Jul 2022 06:59:38 GMT

Anonymous
Please try

Earleist Start = SELECTCOLUMNS ( SUMMARIZE ( FILTER ( ALL ( 'Table2' ), 'Table2'[Timestamp Code] = "Start" && 'Table2'[Prod No] = SELECTEDVALUE ( 'Table2'[Prod No] ) ), 'Table2'[Prod No], "@TimeStamp", MIN ( 'Table2'[Timestamp] ) ), "@@TimeStamp", [@TimeStamp] )

Re: Calculate function and direct

Anonymous — Thu, 07 Jul 2022 07:38:37 GMT

tamerj1

It seems that a lot of functions won't work with Direct Query. SELECTCOLUMNS isn't working either.