topic Re: Direct Query compatible DAX calculation on time difference in DAX Commands and Tips

Direct Query compatible DAX calculation on time difference

TcT85 — Thu, 21 Apr 2022 14:10:11 GMT

Hi,

Need some help with this DAX formula.

Im using direct query and I have tried to some dax formula that was not compatible with Direct Query.

I need to calculate the minimum cycle time for each product between the PCBserialnumbers.

Product	PcbSerialNumber	Finished Date
Vehicle	5552822	2022-03-21 21:43
Vehicle	5705391	2022-03-21 19:56
Vehicle	5705392	2022-03-21 19:58
Vehicle	5706310	2022-03-21 20:00
Vehicle	5706311	2022-03-21 22:00
Vehicle	5728095	2022-03-21 21:41
Vehicle	5728110	2022-03-21 19:55
Vehicle	5728111	2022-03-21 19:59
Textile	5728117	2022-03-21 22:12
Textile	6794296	2022-03-21 19:36
Textile	6794297	2022-03-21 19:35
Textile	6975356	2022-03-21 21:38
Textile	6975401	2022-03-21 21:53
Textile	6975560	2022-03-21 19:47
Textile	6975561	2022-03-21 19:49
Textile	6976247	2022-03-21 21:00
Textile	6976248	2022-03-21 21:03
Textile	6976337	2022-03-21 20:44
Textile	6976338	2022-03-21 20:48
Textile	7222877	2022-03-21 21:21

Re: Direct Query compatible DAX calculation on time difference

amitchandak — Thu, 21 Apr 2022 15:37:18 GMT

TcT85 , can you explain the calculation?

Re: Direct Query compatible DAX calculation on time difference

TcT85 — Fri, 22 Apr 2022 05:47:42 GMT

Hi,

I followed microsoft suggestion to first create this colum

DAX Date Previous1 =

CALCULATE (

MAX ( PD_PcbProductionData[SEL_LOD_LatestStartDateTime] ),

ALLEXCEPT ( PD_PcbProductionData, PD_PcbProductionData[PcbSerialNumber] ),

PD_PcbProductionData[SEL_LOD_LatestStartDateTime] < EARLIER ( 'PD_PcbProductionData'[SEL_LOD_LatestStartDateTime] ))

But here it says Calculate is not allowed in a DAX expression for Direct query model.

If previous column worked i would try to follow up with this secondary dax formula.

Date Diff in Days =
IF (
ISBLANK ( 'View Name'[Date Previous] ),
1,
DATEDIFF (
'View Name'[Date Previous],
'View Name'[Date],
DAY
)
)

But I would replace DAY with Second

Not sure if this would work though

Re: Direct Query compatible DAX calculation on time difference

Icey — Tue, 26 Apr 2022 02:30:22 GMT

Hi TcT85 ,

Please check if this is what you want:

DAX Date Previous1 = VAR CurDate_ = MAX ( PD_PcbProductionData[Finished Date] ) VAR CurProduct_ = MAX ( PD_PcbProductionData[Product] ) RETURN CALCULATE ( MAX ( PD_PcbProductionData[Finished Date] ), PD_PcbProductionData[Product] = CurProduct_, PD_PcbProductionData[Finished Date] < CurDate_, ALLSELECTED ( PD_PcbProductionData ) )

EARLIER function is mostly used in the context of calculated columns. It is not supported in this scenario.

Best Regards,

Icey

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

Re: Direct Query compatible DAX calculation on time difference

TcT85 — Tue, 26 Apr 2022 06:30:12 GMT

Hi Icey,

Thanks for the DAX formula.

This is the key for me to calculate the cycle time.

When i use Datediff to get the cycle time number, it says that function calculate is not allowed for a new column. in direct query model.

Re: Direct Query compatible DAX calculation on time difference

Icey — Tue, 26 Apr 2022 09:13:22 GMT

Hi TcT85 ,

Use MAX() / MIN() function like so:

Measure = DATEDIFF ( MAX ( PD_PcbProductionData[Finished Date] ), [DAX Date Previous1], SECOND )

Best Regards,

Icey

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

Re: Direct Query compatible DAX calculation on time difference

TcT85 — Tue, 26 Apr 2022 11:39:55 GMT

Yes, it works!!! Thanks Icey!

I added ABS to remove the minus value, not sure if this is the best solution.

Dax Measure = VAR Datedifferent= DATEDIFF ( MAX( PD_PcbProductionData[SEL_LOD_LatestEndDateTime] ), [DAX Date Previous2], SECOND ) Return ABS(Datedifferent)

One more question.

How can i Sum all the measure values?

Dax Total Measure = CALCULATE(PD_PcbProductionData[Dax Measure])

I tried to sum the measure values, but it doesnt calculate correct.

For some reason it only takes the latest value that is 556 at the image below.

Re: Direct Query compatible DAX calculation on time difference

Icey — Wed, 27 Apr 2022 01:54:12 GMT

Hi TcT85 ,

Try something like this:

DAX Total Measure = SUMX ( ALLSELECTED ( PD_PcbProductionData[PcbSerialNumber] ), [DAX Measure] )

Best Regards,

Icey

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