Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
ldwf
Helper V
Helper V

DAX code for measure not working

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

vyangliumsft_0-1660724941447.png

 

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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:

vyangliumsft_0-1660724941447.png

 

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

Ashish_Mathur
Super User
Super User

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Unfortunately, for security reasons, I can't provide the .pbix file

ryan_mayu
Super User
Super User

@ldwf 

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?





Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Unfortunately for security reasons, I can't upload the file.  

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.