Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

If statement and count in DAX

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


 

Need Help
  • vojtechsima's avatar
    vojtechsima
    1 year ago

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

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

4 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    1 year ago

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

    CALCULATE(
DISTINCTCOUNT('Table'[Description]),
'Table'[Status] = "Busy"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • villa1980's avatar
    villa1980
    Resolver II
    1 year ago

    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

     

  • villa1980's avatar
    villa1980
    Resolver II
    1 year ago

    I have answered my own question 🙂

    By using sumx this has resolved it 🙂