If statement and count in DAX

[villa1980]

Hi,

 I have a table with a lot of Descriptions and for the current month I would like to count these descriptions if another column (status) states "Busy". However, when I use an if statement with a filter on the visual for current month and then count distinct my DAX if stament, it includes another status within the total. I think I need to use a filter within DAX, but am unsure of where to do this. My if statment is below

if ( 'Table'[Status] = "Busy",'Table'[Description],0)
The outcome should look like
 Busy  = 14 (Distinct descriptions)
However I am seeing
 Busy = 15 (When I bring back the status, it seems to be counting another status 'Other')

Thanks

Alex

[villa1980]

I have answered my own question 🙂

By using sumx this has resolved it 🙂

[villa1980]

Thank-you for the replies, they have really helped out.
I have another issue related to this.

I have created a calendar table linked by date to the table where these fields above sit. I have 2 metrics, blocked and booked by mins and depots and Description.

There are 8 depots in total for Description 1
Total Blocked for this month =  25470 for Description 1

Total Booked for this month = 35730 for Description 1

Available = Total Mins for whole month - (Blocked and Booked)
My Result should be 282,000
The result i get is = -18,300

I can see that the total mins for the whole month is 42,900 (which comes from my calendar table), however, it should be 343,200. It looks like the total mins for the whole month from the calendar table is not summing up by the depots, it is taking it as a single figure.

Any ideas?

Thanks

Alex

[Anonymous]

Hi @villa1980 ,

Based on your description, I created these data.

You can create a measure.

BusyCount = CALCULATE(
    COUNT('Table'[Description]),
    FILTER(ALL('Table'),'Table'[Status] = "Busy" &&
    MONTH('Table'[Date]) = MONTH(TODAY()))
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[vojtechsima · Accepted solution]

Hello, @villa1980 
the most straightforward thing that comes to my mind is this:

CALCULATE(
DISTINCTCOUNT('Table'[Description]),
'Table'[Status] = "Busy"
)