convert sql statement into DAX

[JulietZhu]

 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

[v-juanli-msft]

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

[Greg_Deckler]

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