The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a table similar to the following
PERSON ID | CALENDAR ID | STATUS | RATE | MostRecentOpenRate |
1 | 1 | VOID | 3.00 | |
1 | 2 | OPEN | 4.50 | |
1 | 3 | BUSY | 2.00 | 4.50 |
2 | 1 | OPEN | 6.50 | |
2 | 2 | DOWN | 3.00 | 6.50 |
2 | 3 | BUSY | 2.00 | 6.50 |
2 | 4 | OPEN | 5.50 | |
2 | 5 | BUSY | 1.75 | 5.50 |
For each row, I want a column to give me the the most recent Rate for the same person where Status = OPEN from an earlier Calendar ID. The table I have is already sorted by person and calendar ID.
I've shown the column I want above for MostRecentOpenRate. How can I use DAX to calculate the value for MostRecentOpenRate?
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file whether it suits your requirement.
It is for creating a calculated column.
MostRecentOpenRate CC =
VAR _personid = Data[PERSON ID]
VAR _calendarid = Data[CALENDAR ID]
VAR _recentopencalendarid =
MAXX (
FILTER (
Data,
Data[PERSON ID] = _personid
&& Data[CALENDAR ID] < _calendarid
&& Data[STATUS] = "OPEN"
),
Data[CALENDAR ID]
)
RETURN
IF (
NOT Data[STATUS] = "OPEN",
MAXX (
FILTER (
Data,
Data[PERSON ID] = _personid
&& Data[CALENDAR ID] = _recentopencalendarid
),
Data[RATE]
)
)
Your solution is so great Jihwan_Kim and Ashish_Mathur
Hi, @TCavins
Thank you so much for raising your questions in the community! If Super user's reply solves your doubt, you can mark it as a "solution" so that other friends who encounter similar problems can also find the answer. This will go a long way in helping others in the community. Thank you again for participating!
Best Regards
Jianpeng Li
Hi,
Use this calculated column formula
Column = if(Data[STATUS]="Open",BLANK(),LOOKUPVALUE(Data[RATE],Data[CALENDAR ID],CALCULATE(MAX(Data[CALENDAR ID]),FILTER(Data,Data[PERSON ID]=EARLIER(Data[PERSON ID])&&Data[CALENDAR ID]<EARLIER(Data[CALENDAR ID])&&Data[STATUS]="Open")),Data[PERSON ID],Data[PERSON ID]))
Hi,
Please check the below picture and the attached pbix file whether it suits your requirement.
It is for creating a calculated column.
MostRecentOpenRate CC =
VAR _personid = Data[PERSON ID]
VAR _calendarid = Data[CALENDAR ID]
VAR _recentopencalendarid =
MAXX (
FILTER (
Data,
Data[PERSON ID] = _personid
&& Data[CALENDAR ID] < _calendarid
&& Data[STATUS] = "OPEN"
),
Data[CALENDAR ID]
)
RETURN
IF (
NOT Data[STATUS] = "OPEN",
MAXX (
FILTER (
Data,
Data[PERSON ID] = _personid
&& Data[CALENDAR ID] = _recentopencalendarid
),
Data[RATE]
)
)
Thank you for your response! It's pretty close to where I was going using variables instead of EARLIER, however I was not using MAXX and I think this reads easier that what I was attempting to do.