Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create new table or column based on multiple conditions

Hello.

 

I have 3 tables.

 

Table 1: UserID, SubscriptionID, QuestionID, Rating, DateRated

Table 2: SessionID, SubscriptionID

Table 3: SessionID, QuestionID, QuestionCorrect, DateAnswered

 

Table 1 and 2 joined on SubscriptionID, table 2 and 3 on SessionID.

 

The first table has the ratings a user has given to questions and on what date. The second table has general info about the sessions (there are other columns I haven't listed here but are not relevant to what I need to do) and the third table has info about what questions have been answered during a session, on what date and if they were answered correctly or not.

 

What I need is to either create a new table which has the columns UserID, SubscriptionID, QuestionID, Rating, DateRated, QuestionCorrect or add to the first table a column with the QuestionCorrect.

 

Someone can have answered questions that has not rated. So he can have answered 500 questions and rated 10 of them.

 

Someone can have answered a question multiple times, so I need to check that he rated the question the same day he answered it. For example, I answer it incorrectly on 02/05/2019 and then I answer the question correctly on 04/05/2019 and I rate it that day. On the new table/column it should show that I rated that question and have answered it correctly. 

 

Basically my end goal is to know if someone is more prone to rate a question if he has answered it correctly or if he has incorrectly. So I need a table that has each user and a count of the questions he has rated divided in two different columns, one for ratings he has given to questions he answered correctly and one for questions he answered incorrectly.

 

Thanks.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    I managed to do it.

     

    Here is the solution for anyone interested.

     

    I made a SubscriptionID column in the third table and then I added a calculated column to the first one with this DAX:

     

    Flag = 
MAXX (
    FILTER (
        'Table 3',
        'Table 3'[SubscriptionID] ='Table 1'SubscriptionID]
            && 'Table 3'[DTS].[DateAnswered] = 'Table 1''[DTS].[DateRated]
            && 'Table 3'[QuestionID] = ''Table 1'[QuestionID]
    ),
   'Table 3'[QuestionCorrect]
)

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I managed to do it.

     

    Here is the solution for anyone interested.

     

    I made a SubscriptionID column in the third table and then I added a calculated column to the first one with this DAX:

     

    Flag = 
MAXX (
    FILTER (
        'Table 3',
        'Table 3'[SubscriptionID] ='Table 1'SubscriptionID]
            && 'Table 3'[DTS].[DateAnswered] = 'Table 1''[DTS].[DateRated]
            && 'Table 3'[QuestionID] = ''Table 1'[QuestionID]
    ),
   'Table 3'[QuestionCorrect]
)