Forum Discussion

juhoneyighot's avatar
juhoneyighot
Helper III
2 years ago
Solved

DAX Count Formula for a Specified Row

Hello!

 

I need help with the correct Count DAX formula ont this.I have this table (Time entry) I need to have the following:

 

1 Time-Off TYpe Column: This is a conditional formula if the Type is Vacation, Status is Approve and if Description is Sick Leave then Time-Off Type is Sick Leave else is Casual.

 

2. I have to get the count the number of rows with Casual and number of rows with Sick Leave.

 

Thank you,

 

Juhoney

Tips and Tricks
  • JamesFR06's avatar
    JamesFR06
    2 years ago

    Hello

     

    To get the total for casual and SL, open a matrice put the column To Type in it and the measur I gave you. it will give you the total

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, juhoneyighot 

    Pls have you solved this problem now? you can refer to JamesFR06 reply, thanks for your positive reply. If it doesn't solve the problem, you can refer to my method below.

     

    Time Off Type = 
IF(
    'Table'[Description] = BLANK()
    ||'Table'[Type] = BLANK()
    ||'Table'[Status] = BLANK(),
    BLANK(),
    IF(
        'Table'[Type] = "Vacation"
            && 'Table'[Description] = "Sick Leave"
            && 'Table'[Status] = "Approve",
        "Sick Leave",
        "Casual"
    )
)

     

    count of Casual = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Time Off Type] = "Casual" )
)

count of Sick Leave = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Time Off Type] = "Sick Leave" )
)

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

6 Replies

  • JamesFR06's avatar
    JamesFR06
    Resolver IV
    2 years ago

    Hello

     

    To get the total for casual and SL, open a matrice put the column To Type in it and the measur I gave you. it will give you the total

    • juhoneyighot's avatar
      juhoneyighot
      Helper III
      2 years ago

      Oh I'm sorry.

       

      My apology, Only thos Vaction with approved status will have an Sick Leave and Casual in time Off Type

      I hope you will help me ont his. My biggest apology.

       

      Thank you,

       

      Juhoney

       

      • JamesFR06's avatar
        JamesFR06
        Resolver IV
        2 years ago

        TO Type = IF(AND(msdyn_timeentry[Type]="Vacation", and(msdyn_timeentry[Entry Status]="Approve", msdyn_timeentry[Description]="Sick Leave")),"Sick Leave",

        IF(AND(msdyn_timeentry[Type]="Vacation", and(msdyn_timeentry[Entry Status]="Approve","Casual"))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, juhoneyighot 

    Pls have you solved this problem now? you can refer to JamesFR06 reply, thanks for your positive reply. If it doesn't solve the problem, you can refer to my method below.

     

    Time Off Type = 
IF(
    'Table'[Description] = BLANK()
    ||'Table'[Type] = BLANK()
    ||'Table'[Status] = BLANK(),
    BLANK(),
    IF(
        'Table'[Type] = "Vacation"
            && 'Table'[Description] = "Sick Leave"
            && 'Table'[Status] = "Approve",
        "Sick Leave",
        "Casual"
    )
)

     

    count of Casual = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Time Off Type] = "Casual" )
)

count of Sick Leave = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( 'Table', 'Table'[Time Off Type] = "Sick Leave" )
)

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • JamesFR06's avatar
    JamesFR06
    Resolver IV
    2 years ago

    Hi

     

    If(and([type]="Vacation",and([Status]="Approve,[Description]="Sick Leave")),"Sick Leave", "Casual)

     

    to get the count =countrows(filter([Table],[Time Off Type]="Sick Leave")

    • juhoneyighot's avatar
      juhoneyighot
      Helper III
      2 years ago

      Hello!

      Ive tried the formula but it wont filter

      here is the formula that ive entered:

      TO Type = IF(AND(msdyn_timeentry[Type]="Vacation", and(msdyn_timeentry[Entry Status]="Approve", msdyn_timeentry[Description]="Sick Leave")),"Sick Leave","Casual")

       

      juhoney