Forum Discussion

Cromero3000's avatar
Cromero3000
Icon for Helper I rankHelper I
6 years ago
Solved

How to measure attendance

Hello team!,

I'm no expert with DAX or doing measures and I'm starting to build a dashboard for attendance. Let me give you an example of what I need:

* There are 5 modules

* Each module has different ammount of classes to attend

* All students have to attend to all 5 modules and all classes

 

All my tables are already linked and working fine but I'm struggling with the main measure. The closest that I have is: 

Assist_Mod = Calculate(count(Student[name]), Assistance[ClassName])

The problem with the above code is that it only shows me "1" attendance per student for the whole module:

 

I want it to show the ammount of classes they attended per module and the module 1 has 4 classes so, it has to show number 4 in each cell for "Mod 1" column, not "1". 

 

I hope I have explained myself correctly, I will appreciate If someone can give me some guidance. Thanks in advance!

 

Regards

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Cromero3000 

    As tested, the "Assistance" table and "Disciples" table should modify their relatiosnhip as below:

     

    Then you could create a measure

    Discount =
CALCULATE (
    DISTINCTCOUNT ( Assistance[Discipulo] ),
    FILTER (
        ALLSELECTED ( Assistance ),
        Assistance[Tema] = MAX ( Assistance[Tema] )
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Cromero3000 

    As tested, the "Assistance" table and "Disciples" table should modify their relatiosnhip as below:

     

    Then you could create a measure

    Discount =
CALCULATE (
    DISTINCTCOUNT ( Assistance[Discipulo] ),
    FILTER (
        ALLSELECTED ( Assistance ),
        Assistance[Tema] = MAX ( Assistance[Tema] )
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Cromero3000's avatar
      Cromero3000
      Icon for Helper I rankHelper I
      6 years ago

      Hello v-juanli-msft ,

      Thanks a lot, this worked great!. I just have two question:

      * Why does it works changing the relationship with the "Disciples" field? I mean, the common thing is to use the code, not the description in another column 🤔. I mean, the Disciples field is actually the full name of the student.

      * It shows correctly the classes that they attended to each module but, I see the total shows also as 4 instead of a summ. Is this related with the field being used or is it because of the "Max" factor in the measure? 

       

      Thank you!

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Cromero3000 

        2.

        the total is distinctcount of [Discipulo] for per [Module Name].

        This total is correct since there are only 4 different [Discipulo] for per [Module Name].

        If you want to sum up the value and show 16 on the total, you could create a measure

        Measure = IF(ISINSCOPE(Disciples[Disciples]),[Discount], SUMX(ALL(Disciples[Disciples]),[Discount]))

        Best Regards

        Maggie

        Community Support Team _ Maggie Li

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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Cromero3000 

    1.

    because at the beginning, you create a relationship for table "Assistance" and "Disciples" based on 

    "Assistance"[Codigo Discipulo] and "Disciples"[Code],

    But you can see from the following picture, "Assistance"[Codigo Discipulo] and "Disciples"[Code] are not consisent, 

    when modifying the relationship to "Assistance"[Discipulo] and "Disciples"[Disciples], it works.

    "Assistance"[Discipulo] and "Disciples"[Disciples] are consisent.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Cromero3000's avatar
      Cromero3000
      Icon for Helper I rankHelper I
      6 years ago

      Thank you v-juanli-msft  !, I didn't realize the codes were not the same, I'll proceed and fix that. Thanks again for your help with this!