Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate function and direct

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!

 

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    Plrease try

    Earleist Start =
CALCULATE (
    MIN ( 'Table2'[Timestamp] ),
    'Table2'[Timestamp Code] = "Start",
    REMOVEFILTERS ( 'Table1' ),
    VALUES ( 'Table1'[Prod No] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 

        Please try

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