Forum Discussion

rgalvez's avatar
rgalvez
Frequent Visitor
5 years ago
Solved

New column counting values from two different tables

Hello,

This is my fitst post, I hope I can get the help I need. This is my case:

 

I have Table Enrollments linked to Quiz and to Excersise. Type A or B define whether the user did a Quiz (A) or an Excecise (B).

 

I need a new column Attempts in Enrollments that tells me:

1. How many times one Quiz (Type A) was executed by a user

2. How many times one Excersise (Type B) was executed by a user.

 

Like this:

 

LOOKUPVALUE does the work but it's extremley demanding for my model and I need a better approach.

 

Thanks for your help!

 

RG

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi rgalvez ,

     

    You can create the following calculated column:

     

    Attempts = SWITCH(Enrollments[Type],"A",CALCULATE(COUNTROWS(Quiz),FILTER(Quiz,Quiz[UserID] = EARLIER(Enrollments[UserID]))),"B",CALCULATE(COUNTROWS(Excersise),FILTER(Excersise,Excersise[UserID] = EARLIER(Enrollments[UserID]))))+0

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    rgalvez you can simply add two measure and use it in the table visual

     

    Count Attempts = COUNTROWS ( TableAttempt )

Count Exercise = COUNTROWS ( TableExercise )

     

    To visualize, use table visual, put column from user table and above measures.

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • rgalvez's avatar
      rgalvez
      Frequent Visitor
      5 years ago

      Thanks for your reply!

      I don't think this option helps. I need the two measures in the table, not in the view.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi rgalvez ,

     

    You can create the following calculated column:

     

    Attempts = SWITCH(Enrollments[Type],"A",CALCULATE(COUNTROWS(Quiz),FILTER(Quiz,Quiz[UserID] = EARLIER(Enrollments[UserID]))),"B",CALCULATE(COUNTROWS(Excersise),FILTER(Excersise,Excersise[UserID] = EARLIER(Enrollments[UserID]))))+0

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai