Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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
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
User | Count |
---|---|
73 | |
70 | |
38 | |
23 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |