Forum Discussion
Create Measure from data across multiple rows.
I have a set of learning data in which learners are assigned between 1-3 Class names. Because these classes are tied to security roles, they technically have to complete all 3, but the leadership on the training project have decided if they attend ANY of the classes, they should get credit for all of them.
In order to create a Registration verification (find anyone NOT registered in a class), I am trying to create a formula which looks at the Student ID and correlates any row in which they ARE registered to show even in rows where they are not.
So I need to end up with columns like this:
| Student ID | Course Name | Class ID | Registered? |
| ABC123 | Course1 | 1234 | Yes |
| ABC123 | Course2 | Yes | |
| MNO789 | Course1 | No | |
| MNO789 | Course2 | No | |
| ZYX543 | Course1 | Yes | |
| ZYX543 | Course2 | Yes | |
| ZYX543 | Course3 | 5555 | Yes |
I suspect I need to make a variable inside a CALCULATE. So....
=Calculate(
var CountCourses = COUNTX('TABLE',ALLNOBLANKROW('TABLE'[Class ID]))
var UniqueUser = DISTINCT('TABLE'[User ID})
var TotalRegs = IF(UniqueUser >= [1 CountCourses], "Registered","Not Registered")
RETURN TotalRegs)
As you can see - I am not sure how to write that part of my formula. I suspect there is probably a function I either don't know at all or I just am not seeing the use in this formula.
Thanks for the reply from Ashish_Mathur . Here I have another idea in mind, and I would like to share it for reference.
LMSReportsHelp , you can try to create a measure as follows.
Registered? = VAR _count = CALCULATE(COUNT('Table'[Student ID]), FILTER(ALLEXCEPT('Table', 'Table'[Student ID]), [Class ID] <> BLANK())) RETURN IF(_count <> BLANK(), "Yes", "No")Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi,
Write this calculated column formula
Column = if(CALCULATE(CALCULATE(COUNTROWS(Data),Data[Class ID]<>BLANK()),FILTER(Data,Data[Student ID]=EARLIER(Data[Student ID])))>0,"Yes","No")Hope this helps.
The only variation I can think which (haven't been asked for but I can see could be asked for) is instead of "Yes" a count of HOW MANY CLASSES. THIS project has assigned classes (people can't self-register), but that also allows for human error of accidentally assigning someone to 2+ classes.... Right now the project owner is saying "just yes or no" and in my eyeballing the data I don't see any duplicate assignments - but I can see it happening/being needed. When I tried to switch "Yes" to a Countrows, I got an error.
Try this
Column = CALCULATE(CALCULATE(COUNTROWS(Data),Data[Class ID]<>BLANK()),FILTER(Data,Data[Student ID]=EARLIER(Data[Student ID])))
Thanks for the reply from Ashish_Mathur . Here I have another idea in mind, and I would like to share it for reference.
LMSReportsHelp , you can try to create a measure as follows.
Registered? = VAR _count = CALCULATE(COUNT('Table'[Student ID]), FILTER(ALLEXCEPT('Table', 'Table'[Student ID]), [Class ID] <> BLANK())) RETURN IF(_count <> BLANK(), "Yes", "No")Output:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
