DAX Measure

[Anonymous]

Hi there,

I have a table 1 with a year column from 2020 to 2050, it also include existing ratio's for column A, B and C and the ratio of the three add up to 1 on each year. 

I created two what if parameters for target year and the target year's ratio. For example, I want in 2030, the target ratio for feild C is 50%, based on this target, the new ratio for each year of feild C has been calculated by a measure (e.g. new traget ratio measure). The target ratio measure for each year is the following pic. 

I have table 2 includes years only for 5 year increments from 2020, 2025 until 2050. The table 2 includes other field as well. I want to apply the target ratio measure to a field in table 2 based on year. For example, I want the target ratio for 2030 is 0.5 and 2050 is 0.78. I know the measure is not a column and it cannot use the lookupvalue in DAX. At the moment when I check what is the target ratio for each year in table 2, is the following pic. 

I want to apply the following tables target ratio to table 2, how can I do that?  @AllisonKennedy @amitchandak @AlexisOlson @Greg_Deckler 

Thanks in advance.

Weijia

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[Anonymous]

Hi @lbendlin,

Thanks for your reply. I am new to the Power BI community and hopefully the like below can be accessed. 

dax measure sample data 

Kind regards,

Weijia

[lbendlin]

In your data model

you are missing a connection between "cal vehkm" and your tables influencing the measure.  A telltale sign of an incomplete data model is when you see the same number in all rows.

[Anonymous]

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