cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
izhilin Frequent Visitor
Frequent Visitor

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

2 REPLIES 2
Community Support Team
Community Support Team

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

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

8.png

 

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

Community Support Team
Community Support Team

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

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 135 members 1,649 guests
Please welcome our newest community members: