Need help with DAX measure

[Vijay08V]

Hi all,

I need some help with DAX measure. Any help is high appreciated

Problem statement : To find the average score of each element based on the latest Id for each distinct location and show it in bar graph for different elements

Example :

For Digital element Id 73 and 74 are the latest one for US and CA respectively. Then my calculation for average score for digitial element would be

(3+4)/2 = 3.5 --- Numerator is 2 because of two different distinct locations

For Engagement element Id 73 and 77 are the latest one for US and CA respectively. Now my average score for engagement element would be

(3+3)/2 = 3

Element	Score	Id	location
Digital	3	73	US
Digital	4	74	CA
Engagement	3	73	US
Engagement	3	77	CA
Engagement	4	74	CA

[Vijay08V]

@ChristianDGreat - The latest record will be determined by Id number. If you see for US location for engagement element Id 73 is the latest record and for CA location we have two records with ID 74 and 77 but record with ID 77 will be the latest one

[Ashish_Mathur]

Hi,

For determining the latest record, there should be a Date column as well.  Why have you not shown that?

[Vijay08V]

@Ashish_Mathur  - The latest record will be determined by Id number 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Vijay08V]

@Ashish_Mathur - Hi Ashish. Thanks its working!! Just one doubt here. Do I need to create a separate Id table ?? because I was able to get the required output from the main table Id column itself 

[Ashish_Mathur]

Hi,

As a good practise, I always do so.  Helps in relating multiple Fact Tables to this Dim Table.

[Vijay08V]

@Ashish_Mathur  - Hi Ashish - I couldn't find any Pbi file in the attchement. It shows as Zipped file when I download. Could you pls reupload it as Pbix file

[ChristianDGreat]

On Engagement how do you know 77 and 73 are the latest one?