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 = CALCULAT...
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

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())
      )