Forum Discussion
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
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
9 Replies
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?
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.
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
