Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Colleagues,
I have 2 tables in my data model:
I need to display a line chart:
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |