Filter multiple conditions for same column

[GunnerJ]

I'm needing to write a measure that counts the distinct number of "BI_ACCT" that have a "BI_TASK_CD" = 'MW_FBRDROP' and the "BI_WORK_EVENT_CD" of 'COMP' while the task CD for '1STCONTACT' of the same account number equals 'QUEUE'.

I've created a sample file below. In this example I'd only count 1 BI_ACCT (Account# 123) since it's MW-FBRDROP task is COMP and its 1STCONTACT task is QUEUE. I wouldn't count account# 456 since both tasks are completed. 

BI_ACCT	BI_TASK_CD	BI_WORK_EVENT_CD
123	MW-FBRDROP	COMP
123	1STCONTACT	QUEUE
456	MW-FBRDROP	COMP
456	1STCONTACT	COMP

Any help in this task would be appreciated. I know how to limit just one set of criteria but I'm a bit unsure how to do two sets containing the same columns. Thank you and please let me know if I need to provide any additional details. 

[Jihwan_Kim]

Hi, @GunnerJ 

Please check the below picture and the sample pbix file's link down below.

Result =

VAR currentacct =

MAX ( 'Table'[BI_ACCT] )

VAR newtable =

COUNTROWS (

FILTER (

ALL ( 'Table' ),

'Table'[BI_ACCT] = currentacct

&& 'Table'[BI_TASK_CD] = "MW-FBRDROP"

&& 'Table'[BI_WORK_EVENT_CD] = "COMP"

)

)

VAR newtable2 =

COUNTROWS (

FILTER (

ALL ( 'Table' ),

'Table'[BI_ACCT] = currentacct

&& 'Table'[BI_TASK_CD] = "1STCONTACT"

&& 'Table'[BI_WORK_EVENT_CD] = "QUEUE"

)

)

VAR resulttable =

CALCULATETABLE (

VALUES ( 'Table'[BI_ACCT] ),

FILTER (

ALL ( 'Table' ),

'Table'[BI_ACCT] = currentacct

&& newtable > 0

&& newtable2 > 0

)

)

RETURN

CALCULATE ( COALESCE ( COUNTROWS ( resulttable ), 0 ) )

Result Total Fix =

SUMX( VALUES('Table'[BI_ACCT]), [Result])

https://www.dropbox.com/s/7tsst6oy55vtojl/gunnerjv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Fowmy · Accepted solution]

@GunnerJ 

I created the following measure to count the distinct BI_ACC values based on the criteria, please check and let me know.

BI_Acc_Dcount 2 = 
var __t = 
	CALCULATETABLE(
        VALUES(Table2[BI_ACCT]),
        Table2[BI_TASK_CD]="MW-FBRDROP",Table2[BI_WORK_EVENT_CD]="COMP"
    )
var __result = 
    CALCULATE(
        DISTINCTCOUNT(Table2[BI_ACCT]),
        Table2[BI_TASK_CD]="1STCONTACT",Table2[BI_WORK_EVENT_CD]="QUEUE",
        __t
    )
return
    __result