How to reference a column in a Power BI Measure

[AlvinLy]

Hello Microsoft Community,

It feels like the more I learn about Power BI and DAX, the less I actually know. 

I've been working with measures recently since one of our Power BI model references data in Direct Query mode rather than Import Mode, therefore I've been transforming some of my calculated columns to measures to accomodate this. One issue I've been coming up with is referencing another column in another measure.

So here is the scenario. I have a fact table with activity ID, schedule date, and project ID. Where project ID, connects to a dimension table used as a filter to see which activity belong in which project. I have a date table that gives us the period end dates from 2021 to 2030. For my company it is every other Friday. I previously had a calculated column that took all schedule date for every activity and show what end period date it belongs to. I converted that to a measure shown below:

End Period Date Measure = 
VAR enddate = SELECTEDVALUE('Fact_Table'[Schedule Date])
RETURN
CALCULATE(
    MIN(Date_Table[End Period]),
    Filter(Date_Table, Date_Table[End Period] > enddate)
)

When I throw this into a Power BI table visual this measure works as intended, similar to a calculated column. The trouble i'm running into is when i filter by project, say i only want schedule activties from project A, the power BI visual shows ALL activities, and if the activity is NOT in project A it simply gives the smallest end period date in my date table. So to give a quick visual since i can't upload my data (directquery to datalake)

What my fact table looks like:

Schedule Activity	Schedule Date	Project
Walk	2024-01-03	A
Eat	2024-01-15	A
Sleep	2024-02-12	B

What my previous calculated column when filtering to Project A looked like:

Schedule Activity	Schedule Date	End Period
Walk	2024-01-03	2024-01-05
Eat	2024-01-15	2024-01-19

What the measure outputs when filtering for Project A looks like:

Schedule Activity	Schedule Date	End Period Date
Walk	2024-01-03	2024-01-05
Eat	2024-01-15	2024-01-19
Sleep	2024-02-12	2021-01-08

I think this problem occurs with the use of "SELECTEDVALUE". The filter, for some reason keeps all schedule activity instead of filtering it out, and since that filter context doesn't have a schedule date for that activity it uses BLANK() as enddate variable. Thats my hypothesis. 

So the TLDR, is there an alternative of SELECTEDVALUE in a measure, in general, and in my specific scenario. I know that max, min, sum, could potentially be used, but in my scenario it won't make sense. 

[AlvinLy]

Hello,

That worked perfectly in my small sample data! I will now try it on my larger dataset, but if anything the issues will be from other inputs and not from this null and filter issue

Thanks,

[amitchandak]

@AlvinLy , Try measure like

End Period Date Measure =
VAR enddate = Max('Fact_Table'[Schedule Date])
RETURN
CALCULATE(
MIN(Date_Table[End Period]),
Filter(all(Date_Table), Date_Table[End Period] > enddate)
)

[AlvinLy]

Hello Amitchandak

Thank you for your suggestion. I get the same result. I'm beginning to wonder if it's because I have a DirectQuery rather than an imported model that I come up with this issue, but it is odd since I think the formula that I have is rather simple.

Other than SELECTEDVALUE, MAX, and MIN, what are other typical ways we would select the column in the same row using a measure? 

I assume the logic to either MAX or MIN is that during the row context of the calculation there is only one value so it chooses that value regardless if it's MAX or MIN. That makes sense in my mind, but not sure why it's not working.

[Anonymous · Accepted solution]

Hi @amitchandak ,thanks for the quick reply, I'll add further.

Hi @AlvinLy ,

Regarding your question, please modify your dax expression.

End Period Date = 
VAR _a = SELECTEDVALUE(FactTable[Schedule Date])
RETURN  IF(ISBLANK(_a),BLANK(),MINX(FILTER('DateTable','DateTable'[Date] >= _a),[Date]))

This is my understanding: the schedule time for 'Sleep' is filtered by the slicer to null. later you use it as a condition for filtering the date table, and since it's null, it doesn't filter any data. So it returns the smallest date in the date table. As long as the result of the measure is not null, it will not be automatically omitted by Power BI.