Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct Count with Date Condition and Employee ID

Hi Guys,

Hope I can pick your brain with this one. I have a staff Attendance report. 

 

On the left pivot I have the amount of distinct members of staff in HR that walked into the office each month. 

I'm trying to figure out a formula to replicate the distinct count for each month in Dax but looks like it's giving me the Total along all months. 

 

My Relationships are this..

 

 

If I change the active relationship to the Month - Month Year it stays the same Attendance figure.

 

So there is something I need to change in the Dax but i can't figure it out. Any ideas?

Thanks, Jim

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you tamerj1 

    You have given me so much of your time and helped me with the Dax calculation below:

     

    Attendance1 =
    SUMX (
        SUMMARIZE (
            'Agile Persona',
            'Agile Persona'[Level4:Structure],
            'Agile Persona'[Month]
        ),
        CALCULATE (
            VAR CurrentMonth =
                SELECTEDVALUE ( 'Agile Persona'[Month] )
            VAR Result =
                CALCULATE (
                    DISTINCTCOUNT ( 'Count Taps'[Employee Reference Number3] ),
                    'Count Taps'[Month - Year] = CurrentMonth
                )
            RETURN
                Result
        )
    )
     
    I also needed to amend my relationship to Active with Level 4 and change the cross filter direction to Single (Agile Personoa Filters Count Taps).
     
    Thanks again, I really appreciate it.

     

     

  • tamerj1's avatar
    tamerj1
    4 years ago

    Nice taking to you Anonymous 
    Here is the final solution using one active relationship with "Level4"

    Attendance1 =
SUMX (
    SUMMARIZE (
        'Agile Persona',
        'Agile Persona'[Level4:Structure],
        'Agile Persona'[Month]
    ),
    CALCULATE (
        VAR CurrentMonth =
            SELECTEDVALUE ( 'Agile Persona'[Month] )
        VAR Result =
            CALCULATE (
                DISTINCTCOUNT ( 'Count Taps'[Employee Reference Number3] ),
                'Count Taps'[Month - Year] = CurrentMonth
            )
        RETURN
            Result
    )
)

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you tamerj1 

    You have given me so much of your time and helped me with the Dax calculation below:

     

    Attendance1 =
    SUMX (
        SUMMARIZE (
            'Agile Persona',
            'Agile Persona'[Level4:Structure],
            'Agile Persona'[Month]
        ),
        CALCULATE (
            VAR CurrentMonth =
                SELECTEDVALUE ( 'Agile Persona'[Month] )
            VAR Result =
                CALCULATE (
                    DISTINCTCOUNT ( 'Count Taps'[Employee Reference Number3] ),
                    'Count Taps'[Month - Year] = CurrentMonth
                )
            RETURN
                Result
        )
    )
     
    I also needed to amend my relationship to Active with Level 4 and change the cross filter direction to Single (Agile Personoa Filters Count Taps).
     
    Thanks again, I really appreciate it.

     

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Nice taking to you Anonymous 
    Here is the final solution using one active relationship with "Level4"

    Attendance1 =
SUMX (
    SUMMARIZE (
        'Agile Persona',
        'Agile Persona'[Level4:Structure],
        'Agile Persona'[Month]
    ),
    CALCULATE (
        VAR CurrentMonth =
            SELECTEDVALUE ( 'Agile Persona'[Month] )
        VAR Result =
            CALCULATE (
                DISTINCTCOUNT ( 'Count Taps'[Employee Reference Number3] ),
                'Count Taps'[Month - Year] = CurrentMonth
            )
        RETURN
            Result
    )
)
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    You need to create that as a measure not a calculated column then you need to view it in a matix (pivot table) visual same as you did in axcel.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Tamer, thanks for your reply but I'm still getting the same issue, even with a measure..

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous 
      Try to use a second CALCULATE for CROSSFILTER i.e. wrap the first CALCULATE (with USERELATIONSHIP) with a new CALCULATE (with CROSSFILTER) 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi tamerj1 

        Not sure I quite understand you, below code is how I interprut what you mean;

        Attendance1 =
        CALCULATE(
        DISTINCTCOUNT('Count Taps'[Employee Reference Number3]),
        USERELATIONSHIP('Agile Persona'[Month],'Count Taps'[Month - Year]),
        CALCULATE(CROSSFILTER('Agile Persona'[Level4:Structure],'Count Taps'[Level 4],BOTH)))
         
        I'm getting an error, CROSSFILTER function can only be used in the calculate function?
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous 

      Can you a screenshot of the data model? At least the oart that concerns these two tables

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi tamerj1 

         

        See below model:

         

         

         

        Thanks for your time on this, I really appreciate it.