Forum Discussion

CaveOfWonders's avatar
CaveOfWonders
Icon for Helper IV rankHelper IV
6 years ago
Solved

Cannot Figure out how to replicate my Excel CountIFS formula

I have the following formula simplified from Excel; I cannot figure out how to replicate this in PowerBI. Any help would be appreciated. All the data is in one table in Power BI. The date qualificati...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi CaveOfWonders ,


    Try this measure.

    Measure = 
CALCULATE(
    COUNTROWS(Sheet1),
    FILTER(
        Sheet1,
        Sheet1[Finish date] >= DATE(2020, 1, 1) && Sheet1[Finish date] < DATE(2020, 2, 1) &&
        Sheet1[Actual Finish] >= DATE(2020, 1, 1) && Sheet1[Actual Finish] < DATE(2020, 2, 1) &&
        Sheet1[System] = "x" && Sheet1[Type] = "y"
    )
)

     

    Best regards,
    Lionel Chen

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

  • CaveOfWonders's avatar
    CaveOfWonders
    Icon for Helper IV rankHelper IV
    6 years ago

    Thanks Greg,

     

    Hi Greg, thank you for your reply. I'm trying to count, only if:

     

    1 - the date in the Finish Dates Column falls between the 1st and 31st Jan

    2 - the date in the Actual Finish Column falls between the 1st and 31st Jan

    3 - The System column = "x"

    4 - The Type column = "y"

     

    I have tried to create the measure but I'm getting the error (The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.)

     

    Measure =
    CALCULATE (
        COUNT ( 'Sheet 1'[FINISH Date] ),
        (
            'Sheet 1'[FINISH Date] >= DATE ( 2020, 1, 1 )
                && 'Sheet 1'[FINISH Date] < DATE ( 2020, 2, 1 )
        )
            && (
                'Sheet 1'[Actual Finish] >= DATE ( 2020, 1, 1 )
                    && 'Sheet 1'[Actual Finish] < DATE ( 2020, 2, 1 )
            )
            && ( 'Sheet 1'[System] = "x" )
            && ( 'Sheet 1'[Type] = "x" )
    )
    • v-lionel-msft's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi CaveOfWonders ,


      Try this measure.

      Measure = 
CALCULATE(
    COUNTROWS(Sheet1),
    FILTER(
        Sheet1,
        Sheet1[Finish date] >= DATE(2020, 1, 1) && Sheet1[Finish date] < DATE(2020, 2, 1) &&
        Sheet1[Actual Finish] >= DATE(2020, 1, 1) && Sheet1[Actual Finish] < DATE(2020, 2, 1) &&
        Sheet1[System] = "x" && Sheet1[Type] = "y"
    )
)

       

      Best regards,
      Lionel Chen

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

      • CaveOfWonders's avatar
        CaveOfWonders
        Icon for Helper IV rankHelper IV
        6 years ago

        Thank you so much Lionel, it worked perfectly.