Forum Discussion

LMSReportsHelp's avatar
LMSReportsHelp
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Ashish_Mathur
Super User
2 years ago

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's avatar
    LMSReportsHelp
    Frequent Visitor
    2 years ago

    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's avatar
      Ashish_Mathur
      Super User
      2 years ago

      Try this

      Column = CALCULATE(CALCULATE(COUNTROWS(Data),Data[Class ID]<>BLANK()),FILTER(Data,Data[Student ID]=EARLIER(Data[Student ID])))
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      Try this

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