Forum Discussion
convert sql statement into DAX
How to convert the below sql statement into DAX to calculate Average Lag? Thanks
SELECT avg(isnull(datediff(day, b.ClaimSubmitDate, t.PostDate),0)) as AvgLag
FROM [dbo].[Bill] b join [dbo].[Transactions] t
on b.BillID= t.BillID
WHERE t.[TransactionTypeID] = 2
2 Replies
Honestly, any question that starts with "I want to convert this SQL into DAX" generally never go well when all you present is the SQL. Sample data and expected result will get you there quicker. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi JulietZhu
There are two approches.
1.
create a calculated column
related = LOOKUPVALUE(b[ClaimSubmitDate],b[BillID],t[BillI])
Then create a measure
Measure = VAR Table1 = NATURALINNERJOIN ( b, t ) VAR datediff = IF ( ISBLANK ( DATEDIFF ( MAX ( [related] ), MAX ( [PostDate] ), DAY ) ), 0, DATEDIFF ( MAX ( [related] ), MAX ( [PostDate] ), DAY ) ) RETURN CALCULATE ( AVERAGE ( 'Table'[datediff] ), FILTER ( ALL ( 'Table' ), [TransactionTypeID] = 2 ) )2. create a new table
Table = NATURALINNERJOIN(b,t)
Then in the new table
create calculated columns
datediff =
IF (
ISBLANK ( DATEDIFF ( [ClaimSubmitDate], [PostDate], DAY ) ),
0,
DATEDIFF ( [ClaimSubmitDate], [PostDate], DAY )
)average = CALCULATE(AVERAGE('Table'[datediff]),FILTER(ALL('Table'),[TransactionTypeID]=2))Best Regards
maggie
