Forum Discussion

ss89's avatar
ss89
Helper II
3 years ago
Solved

Countifs in DAX

Hello Community,

 

I have to replicate from Excel the same formula 2 (highlined in yellow) in Power BI but I get stuck when I try to convert COUNTIFS in Power BI.

 

 

Many thanks in advance for your support.

 

All the best,

Silvia

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    ss89 ,

    New column =

    var _count = count(filter(Table, [Lot_number] =earlier([LOT Number]) && [Formula] = "FALSE"), [LOT_NUMBER])

    return

    if(isblank(_count), "TRUE", "FALSE")

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, ss89 

     

    You can try the following methods.

    Column = 
VAR _Count =
    CALCULATE ( COUNT ( 'Table'[LOT_NUMBER] ),
        FILTER ( 'Table',
            [FORMULA] = TRUE
                && [LOT_NUMBER] = EARLIER ( 'Table'[LOT_NUMBER] )
        )
    )
RETURN
    IF ( _Count = 2, TRUE (), FALSE () )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    ss89 ,

    New column =

    var _count = count(filter(Table, [Lot_number] =earlier([LOT Number]) && [Formula] = "FALSE"), [LOT_NUMBER])

    return

    if(isblank(_count), "TRUE", "FALSE")

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, ss89 

     

    You can try the following methods.

    Column = 
VAR _Count =
    CALCULATE ( COUNT ( 'Table'[LOT_NUMBER] ),
        FILTER ( 'Table',
            [FORMULA] = TRUE
                && [LOT_NUMBER] = EARLIER ( 'Table'[LOT_NUMBER] )
        )
    )
RETURN
    IF ( _Count = 2, TRUE (), FALSE () )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

  • ss89's avatar
    ss89
    Helper II
    3 years ago

    Dear v-zhangti , amitchandak 

    many thanks for your help!

    I would like to bring to your kind attention my latest request.

    I should filter only LOT_NUMBER that simultaneously satisfy the following characteristics:

    STAGE NAMECONDITION
    RECEIVINGAPPROVED
    SAMPLINGONLINE or APPROVED
    QC ID_BETONLINE
    QC MICROONLINE
    QC MICRO ADDONLINE
    QCONLINE
    QC BETONLINE
    QC ID_CHEMONLINE
    QC IRONLINE
    QC IDONLINE
    QC ID MICROONLINE
    NIRONLINE
    QC COMPLETEONLINE
    QC ID CHEMONLINE
    QC ID BETONLINE
    QC ID NO MICROONLINE
    QC ID SAMPLEONLINE
    QC CHEMONLINE
    RELEASEONLINE

     

    Many thanks in advance for your support!

    All the best

    • ss89's avatar
      ss89
      Helper II
      3 years ago

      If you can give me instructions on how the file with the data can be inserted, I will attach it in the reply. Sorry but I can't find how to attach a file. Thank you very much!