DAX custom score challenge

[IvanMislav]

Hi,

Here is the situation. I have two tables: "Messages" and "Users" which are related throught User_id.

Mesagges: Message_id, Replied_to_id, User_id

Users: User_id, User_name, Department

What i want to do is to create a measure that will calculate some custom Score. 

Score = If the user replied to a post made by a user from a different department, it counts as 2. If the user replied to a post made by a user from the same department, it counts as 1. If the post does not have Replied_to_id (Initial post), it counts as 0.

Does anyone have any suggestions how to do this?

Thanks!

[technolog · Accepted solution]

To solve this challenge, you'll need to use DAX to compare the department of the original poster to the department of the user who replied. You can do this with the RELATED and RELATEDTABLE functions.

Here's a step-by-step solution.

First, create a relationship between the Messages and Users tables on the User_id field, if you haven't done so already.

Create a calculated column in the Messages table to fetch the department of the user who posted the message:

MessageUserDept = RELATED(Users[Department])
Create another calculated column in the Messages table to fetch the department of the user who the message is a reply to:
RepliedToUserDept =
IF(
ISBLANK(Messages[Replied_to_id]),
BLANK(),
RELATED(Users[Department], LOOKUPVALUE(Messages[User_id], Messages[Message_id], Messages[Replied_to_id]))
)
Now, create the Score measure based on your conditions:
Score =
SUMX(
Messages,
SWITCH(
TRUE(),
ISBLANK(Messages[Replied_to_id]), 0,
Messages[MessageUserDept] = Messages[RepliedToUserDept], 1,
Messages[MessageUserDept] <> Messages[RepliedToUserDept], 2,
BLANK()
)
)
This measure iterates over each row of the Messages table and calculates the score based on your conditions.

By using this measure in a visual, you can calculate the score based on any context you set (e.g., filter by specific user, department, or date ranges).

Remember to adjust table and column names if they're different from the ones you provided.