Create Measure from data across multiple rows.

[LMSReportsHelp]

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.

[v-xuxinyi-msft · Accepted solution]

Hi @LMSReportsHelp 

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 Xu

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

[Ashish_Mathur]

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.

[LMSReportsHelp]

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.

[Ashish_Mathur]

Try this

Column = CALCULATE(CALCULATE(COUNTROWS(Data),Data[Class ID]<>BLANK()),FILTER(Data,Data[Student ID]=EARLIER(Data[Student ID])))

[Ashish_Mathur]

Try this

Column = CALCULATE(CALCULATE(COUNTROWS(Data),Data[Class ID]<>BLANK()),FILTER(Data,Data[Student ID]=EARLIER(Data[Student ID])))