Count number of instance based on multiple criteria

[danextian]

Hi All,

I've been trying to solve this on my own but  I just can't get the right DAX

Here's my scenario:

For each transaction type, the employee should meet the following mix -

transction type mix by employee	Count
Transaction Type 1	2
Transaction Type 2	1

This is going to be on a per month basis. If this mix is not hit by employee and by month, value should be "did not meet" else "did meet"

Here is a sample data - 

Month	Employee	Transaction Type	Count
Jan	A	1	A
Jan	B	1	A
Jan	C	1	A
Jan	D	1	A
Jan	A	2	A
Jan	B	2	A
Jan	C	2	A
Jan	D	1	A
Jan	A	2	A
Jan	B	2	A
Jan	C	2	A
Jan	D	2	A
Feb	C	1	A
Feb	B	1	A
Feb	C	1	A
Feb	D	1	A
Feb	A	2	A
Feb	B	2	A
Feb	C	2	A
Feb	D	1	A
Feb	A	2	A
Feb	B	2	A
Feb	C	2	A
Feb	D	2	A

The resulting remarks should be something like this - 

Month	Employee	Type 1	Type 2	Grand Total	remarks
Jan	A	1	2	3	did not meet
	B	1	2	3	did not meet
	C	1	2	3	did not meet
	D	2	1	3	did meet
Feb	A		2	2	did not meet
	B	1	2	3	did not meet
	C	2	2	4	did not meet
	D	2	1	3	did meet

[Phil_Seamark]

Hi @danextian

Please try the following 4 calculated measures:

Type 1 = CALCULATE(COUNTROWS('Transactions'),'Transactions'[Transaction Type]="1")

Type 2 = CALCULATE(COUNTROWS('Transactions'),'Transactions'[Transaction Type]="2")

Grand Total = [Type 1] + [Type 2]

Remarks = IF([Type 1]>=2 && [Type 2]>=1,"did meet","did not meet")

[danextian]

Hi Phil,

Thanks for the reply. I tried this but it did not work. The measure calculates based on the whole data instead of a, for example, per criteria (by employee, by month). Since, for  the whole, the count for type 1 is not equal to 1 and for type 2 not equal to 2, the result is "did not meet".

[Phil_Seamark]

Hi @danextian

Are the [type 1] and [type 2] measures ok and it's just the [result] measure that is wrong?

[danextian]

Hi @Phil_Seamark

Type 1 and Type are both okay - they both show the correct count. However, the calculated column is showing undesired result.]

If

Employee A

Type 1 = 2

Type 2 = 2

Then remarks = "did not meet"

Both criteria should be met. For this to be "did meet"  type 2 should be 1.  Cannot be less or equal than the set criteria.

[danextian]

PS: Both criteria should be met to have a "did meet" result.

[Phil_Seamark]

Hi @danextian

If the [Remark] calculated measure needs to be an exact match you could try this

Remarks = IF([Type 1] = 2 && [Type 2] = 1,"did meet","did not meet")

[danextian]

Hi @Phil_Seamark,

Thanks again for the prompt response. However, this still did not  work. It seems that this calculated column does not filter on a per employee and per month basis. I created the following matrix:

Row - Employee

Column - Trasaction Type

Value  =  Count of Transactions

The matrix shows that Employee A has 2 Type 1s and 1 Type 2s. Given the logic, this should have resulted to "did meet" but have not. 

I think we can use some sort of "x" function but not sure what the correct formula would be.

[danextian]

Thanks. I will try this out.