Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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.
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.
@TcT85 , can you explain the calculation?
Hi,
I followed microsoft suggestion to first create this colum
But I would replace DAY with Second
Not sure if this would work though
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.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |