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:

  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

Need Help

3 Replies

  • 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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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