DAX Measure

Hi lbendlin,

Thanks for your help. I have made a connection between the "cal vehkm" and "cal ratio table". The "cal ratio table" is the influence table for measures.

It worked partially but not fully as I expected. The results for 2020 and 2030 is what I expected. But for other years 2025, 2030, 2040, 2045 and 2050 are not right. 

I checked the reasons for this and it is due to the part of the measures that I calculated works fine for "cal ratio table" but not for "cal vehkm". Two measures caused the above results. 

The first measure is "zh current year ev existing ratio". The current year is 2021 and it calculated the current ev ratio based on the current year. But there is no year 2021 in the table "cal vehkm" and it shows all blank which treated as 0 in dax. 

The second measure is "zj target year existing ratio". The target year is 2030. It calculated the target year existing ratio based on the selected target year. If any year in the table less than target year, the ratio would be 0, greater or equal to would be the existing 2030 ratio. But this measure did not work for table "cal vehkm" for years after 2030. 

The issue visual table that I checked is below based on the year column is from "cal vehkm" and all other measures are based on "cal ratio table".

The table below is for all years from 2020 to 2050 based on the table "cal ratio table". I would expect the follwing table when I am using the year from the table "cal vehkm". This will correct the initial table issue that some years ev target ratio is not right. 

The following link is the updated sample file with new connection and two new tables to show the above pictures. 

dax measure sample data updated with new connections 

Thanks in advance.

Weijia