locked
DAX custom score challenge RRS feed

  • Question

  • 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!

    Monday, February 13, 2017 9:09 AM

Answers

  • Assuming that you have a relationship between the 2 User_Id columns a measure like the following should work

    Score :=
    SUMX (
        Messages,
        IF (
            ISBLANK ( messages[replied_to_id] ),
            0,
            IF (
                LOOKUPVALUE ( Users[Department], Users[User_id], Messages[Replied_to_id] )
                    = RELATED ( Users[Department] ),
                1,
                2
            )
        )
    )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, February 14, 2017 4:02 AM

All replies

  • Hi IvanMislav,
     
    Based on my understanding, I post the following solution. If this doesn’t resolve your issue, please create sample data, so I can further analysis.

    In Message table, create a calculated column using the formula.

    S=IF(CALCULATE(COUNTA(Message[User_id]),ALLEXCEPT(Message[Replied_to_id]))=1,0,IF(DISTINCTCOUNT(Message[User_id])=1,1,2))
    

    Then create a measure in Users table.

    Score:=CALCULATE(SUM(Mesagges[S]),ALLEXCEPT(Users, Users[User_id]))
    Thanks a lot.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 14, 2017 2:19 AM
  • Assuming that you have a relationship between the 2 User_Id columns a measure like the following should work

    Score :=
    SUMX (
        Messages,
        IF (
            ISBLANK ( messages[replied_to_id] ),
            0,
            IF (
                LOOKUPVALUE ( Users[Department], Users[User_id], Messages[Replied_to_id] )
                    = RELATED ( Users[Department] ),
                1,
                2
            )
        )
    )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, February 14, 2017 4:02 AM