Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Proud to be a Super User!
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")
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".
Proud to be a Super User!
Hi @danextian
Are the [type 1] and [type 2] measures ok and it's just the [result] measure that is wrong?
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.
Proud to be a Super User!
PS: Both criteria should be met to have a "did meet" result.
Proud to be a Super User!
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")
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.
Proud to be a Super User!
Thanks. I will try this out.
Proud to be a Super User!
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |