Countifs in DAX

[ss89]

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

[amitchandak · Accepted solution]

@ss89 ,

New column =

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

return

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

[ss89]

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 NAME	CONDITION
RECEIVING	APPROVED
SAMPLING	ONLINE or APPROVED
QC ID_BET	ONLINE
QC MICRO	ONLINE
QC MICRO ADD	ONLINE
QC	ONLINE
QC BET	ONLINE
QC ID_CHEM	ONLINE
QC IR	ONLINE
QC ID	ONLINE
QC ID MICRO	ONLINE
NIR	ONLINE
QC COMPLETE	ONLINE
QC ID CHEM	ONLINE
QC ID BET	ONLINE
QC ID NO MICRO	ONLINE
QC ID SAMPLE	ONLINE
QC CHEM	ONLINE
RELEASE	ONLINE

Many thanks in advance for your support!

All the best

[ss89]

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!

[v-zhangti · Accepted solution]

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.

[amitchandak · Accepted solution]

@ss89 ,

New column =

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

return

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