Forum Discussion

Elisa112's avatar
Elisa112
Helper V
1 year ago
Solved

Distinct count rows based on condition

Hello Experts 

I am trying to find the count of rows in a table for this month and last month based on a condition but I am getting incorect figures.

Calls this month:

Client Appointed = CALCULATE(
        DISTINCTCOUNT('Calls'[Call ID]),
        FILTER('Calls',MONTH('Calls'[Call Date])=MONTH(TODAY()) && 'Calls'[Outcome] = "appointed"))
 
I also need to find appointed calls last month
 
any suggestions greatly appreiated, thanks in advance
 


Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi Elisa112,
    Please refer to the DAX calculations below for the current and previous month's call volume:

    Current Month Calls:
    Calls in Current month =
    CALCULATE(
    DISTINCTCOUNT('Calls'[Call ID]),
    FILTER('Calls',
    MONTH('Calls'[Call Date]) = MONTH(TODAY()) &&
    YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
    'Calls'[Outcome] = "appointed"
    ))

    Previous Month Calls:

    Calls in Previous month =
    CALCULATE(
    DISTINCTCOUNT('Calls'[Call ID]),
    FILTER('Calls',
    MONTH('Calls'[Call Date]) = MONTH(TODAY()) - 1 &&
    YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
    'Calls'[Outcome] = "appointed"
    ))
    I believe this will achieve the desired outcome.

    Thank you

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Elisa112 -Can you please check the below logic.month(today()) will works.

     

    Calls This Month =
    CALCULATE(
    DISTINCTCOUNT('Calls'[Call ID]),
    FILTER(
    'Calls',
    MONTH('Calls'[Call Date]) = MONTH(TODAY()) &&
    YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
    'Calls'[Outcome] = "appointed"
    )
    )

     

    Hope this helps.

    • Elisa112's avatar
      Elisa112
      Helper V
      1 year ago

      Hello

      thank you but I am still getting incorrect figures, with the condition (appointed) I am getting the same figures without the condition.

      any ideas?

      thanks

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        1 year ago

        Hi Elisa112 -if possible, can you please share the working data pbix file by removing the sensitive data or sample data in excel copy for reference.

        Calls This Month =
        CALCULATE(
        DISTINCTCOUNT('Calls'[Call ID]),
        'Calls'[Outcome] = "appointed",
        MONTH('Calls'[Call Date]) = MONTH(TODAY()),
        YEAR('Calls'[Call Date]) = YEAR(TODAY())
        )

         

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    1 year ago

    Hi Elisa112,
    Please refer to the DAX calculations below for the current and previous month's call volume:

    Current Month Calls:
    Calls in Current month =
    CALCULATE(
    DISTINCTCOUNT('Calls'[Call ID]),
    FILTER('Calls',
    MONTH('Calls'[Call Date]) = MONTH(TODAY()) &&
    YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
    'Calls'[Outcome] = "appointed"
    ))

    Previous Month Calls:

    Calls in Previous month =
    CALCULATE(
    DISTINCTCOUNT('Calls'[Call ID]),
    FILTER('Calls',
    MONTH('Calls'[Call Date]) = MONTH(TODAY()) - 1 &&
    YEAR('Calls'[Call Date]) = YEAR(TODAY()) &&
    'Calls'[Outcome] = "appointed"
    ))
    I believe this will achieve the desired outcome.

    Thank you

    • Elisa112's avatar
      Elisa112
      Helper V
      1 year ago

      Hello

      thank you but I am still getting incorrect figures, with the condition (appointed) I am getting the same figures without the condition.

      any ideas?

      thanks