New column counting values from two different tables

[rgalvez]

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

[v-deddai1-msft · Accepted solution]

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

[rgalvez]

It worked!  Thanks a lot!

[parry2k]

@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]

Thanks for your reply!

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