Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello,
I have a PBI star schema model. i'm trying to define a measure in the fact table using a numeric field (health index) that resides in one of the dimension tables and corresponds to the maximum date value that is selected in the slicer. The measure is not something that can be aggregated. A simple query joining the fact and dimension tables would look like this:
Country Date Health Index
China 3/31/22 6
China 2/28/22 4
China 1/31/22 7
I want to be able to create a measure in the fact table called Maximum Health Index to display the data such that it shows the Health index Value for the maximum date selected in the slicer, so if in the slicer the largest date I select is 3/31/22, I would like to have the measure value be 6:
Country Date Maximum Health Index
China 3/31/22 6
I tried the following DAX formula. It doesn't give me an error message but it gives me erroneous data. I know the join between tables is correct, so don't know what the problem is
Maximum Health Index = var _a = MAXX(ALLSELECTED ('Dim Date Table'[Date]), 'Dim Date Table' [Date]
return
CALCULATE(MAX('Dim Health Indexes Table'[Health Index]), 'Dim Date Table'[Date]=_a
)
Thanks
Solved! Go to Solution.
Hi @ldwf ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _min=MINX(ALLSELECTED('Dim Date Table'),[Date])
var _max=MAXX(ALLSELECTED('Dim Date Table'),[Date])
return
CALCULATE(
MAX('Dim Health Indexes Table'[Health Index]),FILTER(ALL('Dim Health Indexes Table'),
'Dim Health Indexes Table'[Date]=_max))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @ldwf ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _min=MINX(ALLSELECTED('Dim Date Table'),[Date])
var _max=MAXX(ALLSELECTED('Dim Date Table'),[Date])
return
CALCULATE(
MAX('Dim Health Indexes Table'[Health Index]),FILTER(ALL('Dim Health Indexes Table'),
'Dim Health Indexes Table'[Date]=_max))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
Create a Calendar Table with a relationship (Many to One and Single) from the Date column of your Fact table to the Date column of your Calendar Table. In the Calendar Table, create columns for Year, Month name and Month number. Sort the Month name by the Month number. Create slicer for Year and Month name and select a Month and year. To your table visual, drag Country and Date from the Calendar Table. Write this measure
Measure = calculate(sum(Data[Health index]),datesbetween(Calendar[Date],max(calendar[date]),max(calendar[date])))
Hi, thanks for the suggestion. Unfortunately, it doesn't make a difference. I think my DAX formula is almost there, just needs some tweaking. I just want the value from the dimension table that corresponds to the maximum date that is selected in the slicer. I know the joins between the tables are correct so I don't know what hte problem is
Hi,
Share the link from where i can download your PBI file.
Unfortunately, for security reasons, I can't provide the .pbix file
what did you get now?
could you pls provide the sample fact table and dim table?
What if you select 3/30/22, what's the result you want to get?
Proud to be a Super User!
The number is inflated by a lot when I use this DAX formula. The model is star schema. The fact table joins to the date table by a date id, and the fact table joins to the other dimension table by the key. The joins are correct, and I am able to write queries joining all three tables. But I want to create a new measure in the fact table using a field in the dimension table (not the date table) and apply it to the maximum date selected in the slicer. Hope this helps explain
could you pls provide the pbix file?
Proud to be a Super User!
Unfortunately for security reasons, I can't upload the file.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.