Get single value by date filter, similar to SQL LEFT JOIN / OUTER APPLY

[izhilin]

Dear Colleagues,

I have 2 tables in my data model:

1. Calendar (column "Date")
2. Credit limits by customer (columns "Credit limit change date", "Credit limit change time", "Customer Nr", "Old credit limit", "New credit limit") - there can be multiple changes per one customer and date

I need to display a line chart:

• Showing all dates from Calendar
• By Customer
• For each date, show "New credit limit" from Credit limits with "Credit limit change date" on or after Calendar.Date

So, in SQL I would just do:

select D.Date, CLValidAsOfDate."Customer Nr", CLValidAsOfDate."New credit limit"
from Date D
outer apply (select top 1 * from CreditLimits CL where CL."Credit limit change date" >= D.Date order by CL."Credit limit change date" asc, "Credit limit change time" desc) CLValidAsOfDate

What do I do in DAX?

Thank you!

Here is the pbix: https://1drv.ms/u/s!ApX8JJTnPqMHghFkPr1dcc9Ts5jX

[v-juanli-msft]

Hi @izhilin

Does my reply solve your question?

If not, Could you show the result table after applying SQL statement in SQL, so that i would know what i done wrong and modify to get the correct one?

Best Regards

Maggie

[v-juanli-msft]

Hi @izhilin

create a calculated column

sum1 = CALCULATE(SUM('Credit Limits'[New credit limit]),
         ALLEXCEPT('Credit Limits','Credit Limits'[Customer Nr]),
           FILTER('Calendar',[Credit Limit Change Date]>=[Date]))

If you'd like the consider the Credit Limit Change Time, you could add columns

rank1 = var rank1=RANKX(FILTER(ALL('Credit Limits'),'Credit Limits'[Customer Nr]=EARLIER('Credit Limits'[Customer Nr])),[Credit Limit Change Date],,ASC) 
        var rank2=RANKX(FILTER(ALL('Credit Limits'),[Customer Nr]=EARLIER([Customer Nr])&&[Credit Limit Change Date]=EARLIER([Credit Limit Change Date])),[Credit Limit Change Time],,ASC)
        Return rank1+rank2

sum2 = CALCULATE(SUM('Credit Limits'[New credit limit]),FILTER(ALLEXCEPT('Credit Limits','Credit Limits'[Customer Nr]),[rank1]<=EARLIER('Credit Limits'[rank1])))

Best Reagrds

Maggie

[Anonymous]

Hi 

@v-juanli-msft Did this resolve this issue?

i have a similar query i need to convert to DAX which nobody can answer, even the MVP's. Can you help?

SELECT *
FROM Fact f

Cross apply

select top 1
from dimension1 d1
where f.id=d1.id
and datefrom >=@From and dateto <=@To
order by datefrom desc

outer apply

select top 1
from dimension2 d2
where f.id=d2.id
and datefrom >=@From and dateto <=@To
order by datefrom desc

outer apply

select top 1
from dimension2 d2
where f.id=d2.id
and datefrom >=@From and dateto <=@To
order by datefrom desc