Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
1.If same item has mutiple time return with same code then return 1 in first line item only and remaining line items return as a 0.
Example
item 198 3000 A1, 198 4000 A1, 147 6000 A2
2.If same item does not have a mutiple time return with same code then return 1.
I am looking for New calculated column option (DAX)
Example
123 3000 A1, 124 4000 A1
3.If code column equal to "Not Pair" then retun 1.
Example
127 7000 Not Pair, 128 8000 Not Pair, 137 7000 Not Pair
In Excel I applying the following formula =IF(A3="Not Pair",1,IF(MATCH(B3,B:B,0)=ROW(),1,0)).
The formula logic is - if its matches 1st time it will give 1 else 0.
https://www.dropbox.com/s/jherglz8y3zzfvz/Match%20and%20Rows-SOF-25-09-2021.pbix?dl=0
Data
Code | Item | Desired Result (Unique Count) |
A1 | 123 3000 A1 | 1 |
A1 | 124 4000 A1 | 1 |
A2 | 125 5000 A2 | 1 |
A2 | 126 6000 A2 | 1 |
Not Pair | 127 7000 Not Pair | 1 |
Not Pair | 128 8000 Not Pair | 1 |
A1 | 135 3000 A1 | 1 |
A2 | 135 6000 A2 | 1 |
A2 | 136 5000 A2 | 1 |
A2 | 136 6000 A2 | 1 |
A1 | 137 3000 A1 | 1 |
Not Pair | 137 7000 Not Pair | 1 |
Not Pair | 137 8000 Not Pair | 1 |
A1 | 138 4000 A1 | 1 |
Not Pair | 138 7000 Not Pair | 1 |
Not Pair | 138 8000 Not Pair | 1 |
A1 | 139 3000 A1 | 1 |
Not Pair | 139 7000 Not Pair | 1 |
A1 | 140 4000 A1 | 1 |
Not Pair | 140 7000 Not Pair | 1 |
A2 | 141 5000 A2 | 1 |
Not Pair | 141 7000 Not Pair | 1 |
Not Pair | 141 8000 Not Pair | 1 |
A2 | 142 6000 A2 | 1 |
Not Pair | 142 7000 Not Pair | 1 |
Not Pair | 142 8000 Not Pair | 1 |
A1 | 143 4000 A1 | 1 |
A2 | 143 6000 A2 | 1 |
Not Pair | 143 7000 Not Pair | 1 |
A1 | 144 3000 A1 | 1 |
A1 | 144 4000 A1 | 1 |
Not Pair | 144 7000 Not Pair | 1 |
A1 | 145 3000 A1 | 1 |
A1 | 145 4000 A1 | 1 |
A2 | 145 5000 A2 | 1 |
A2 | 145 6000 A2 | 1 |
A2 | 147 6000 A2 | 1 |
A2 | 147 6000 A2 | 0 |
A1 | 148 3000 A1 | 1 |
A1 | 148 3000 A1 | 0 |
A1 | 149 4000 A1 | 1 |
A1 | 149 4000 A1 | 0 |
A1 | 150 3000 A1 | 1 |
A2 | 150 5000 A2 | 1 |
A1 | 151 4000 A1 | 1 |
A2 | 151 6000 A2 | 1 |
A1 | 152 3000 A1 | 1 |
Not Pair | 152 7000 Not Pair | 1 |
A2 | 152 5000 A2 | 1 |
A1 | 153 3000 A1 | 1 |
A1 | 153 4000 A1 | 1 |
A2 | 153 5000 A2 | 1 |
A2 | 1999 5000 A2 | 1 |
A2 | 1999 6000 A2 | 1 |
A1 | 1999 3000 A1 | 1 |
A1 | 155 4000 A1 | 1 |
A2 | 155 6000 A2 | 1 |
A2 | 156 5000 A2 | 1 |
A1 | 156 4000 A1 | 1 |
Not Pair | 157 7000 Not Pair | 1 |
Not Pair | 157 8000 Not Pair | 1 |
A1 | 154 3000 A1 | 1 |
A2 | 154 5000 A2 | 1 |
A1 | 154 4000 A1 | 1 |
A2 | 155 5000 A2 | 1 |
A1 | 155 4000 A1 | 0 |
Not Pair | 155 7000 Not Pair | 1 |
A2 | 156 5000 A2 | 0 |
A1 | 156 4000 A1 | 0 |
Not Pair | 156 7000 Not Pair | 1 |
Not Pair | 156 8000 Not Pair | 1 |
A1 | 198 3000 A1 | 1 |
A1 | 198 3000 A1 | 0 |
A1 | 198 3000 A1 | 0 |
A1 | 198 3000 A1 | 0 |
A1 | 198 3000 A1 | 0 |
A1 | 200 4000 A1 | 1 |
A1 | 200 4000 A1 | 0 |
A1 | 200 4000 A1 | 0 |
A1 | 200 4000 A1 | 0 |
A1 | 200 4000 A1 | 0 |
A1 | 200 4000 A1 | 0 |
A1 | 198 4000 A1 | 1 |
A1 | 198 4000 A1 | 0 |
A1 | 198 4000 A1 | 0 |
A1 | 198 4000 A1 | 0 |
A1 | 198 4000 A1 | 0 |
Solved! Go to Solution.
you need to create an index column in PQ, then use DAX to create a column
Column = if('Table'[Code]="Not Pair",1,if('Table'[Index]=CALCULATE(min('Table'[Index]),ALLEXCEPT('Table','Table'[Item])),1,0))
pls see the attachment below
Proud to be a Super User!
you need to create an index column in PQ, then use DAX to create a column
Column = if('Table'[Code]="Not Pair",1,if('Table'[Index]=CALCULATE(min('Table'[Index]),ALLEXCEPT('Table','Table'[Item])),1,0))
pls see the attachment below
Proud to be a Super User!
Hi. Thanks for your help.
it's working well. Thank you.
you are welcome
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |