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 advanc...
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.

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!