Forum Discussion

izhilin's avatar
izhilin
Frequent Visitor
7 years ago

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

Dear Colleagues,   I have 2 tables in my data model: Calendar (column "Date") Credit limits by customer (columns "Credit limit change date", "Credit limit change time", "Customer Nr", "Old credi...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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