Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JulietZhu
Helper IV
Helper IV

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 2
v-juanli-msft
Community Support
Community Support

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

10.png

11.png

 

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

12.png

 

Best Regards

maggie

Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.