Calculate function and direct

[CRN]

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-...

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!

[tamerj1]

Hi @CRN 
Plrease try

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

[CRN]

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]

@CRN 

Please try

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

[CRN]

Hi @tamerj1 

The MINX function is not supported either.

Unfortunately I still seem to be stuck.

Thanks for your help!

[tamerj1]

@CRN 
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]
)

[CRN]

@tamerj1 

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