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.Exampleitem 198 3000 A1, 198 4000 A1, 147 6000 A22.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) Example123 3000 A1, 124 4000 A13.If code column equal to "Not Pair" then retun 1.Example127 7000 Not Pair, 128 8000 Not Pair, 137 7000 Not PairIn 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=0Data CodeItemDesired Result (Unique Count)A1123 3000 A11A1124 4000 A11A2125 5000 A21A2126 6000 A21Not Pair127 7000 Not Pair1Not Pair128 8000 Not Pair1A1135 3000 A11A2135 6000 A21A2136 5000 A21A2136 6000 A21A1137 3000 A11Not Pair137 7000 Not Pair1Not Pair137 8000 Not Pair1A1138 4000 A11Not Pair138 7000 Not Pair1Not Pair138 8000 Not Pair1A1139 3000 A11Not Pair139 7000 Not Pair1A1140 4000 A11Not Pair140 7000 Not Pair1A2141 5000 A21Not Pair141 7000 Not Pair1Not Pair141 8000 Not Pair1A2142 6000 A21Not Pair142 7000 Not Pair1Not Pair142 8000 Not Pair1A1143 4000 A11A2143 6000 A21Not Pair143 7000 Not Pair1A1144 3000 A11A1144 4000 A11Not Pair144 7000 Not Pair1A1145 3000 A11A1145 4000 A11A2145 5000 A21A2145 6000 A21A2147 6000 A21A2147 6000 A20A1148 3000 A11A1148 3000 A10A1149 4000 A11A1149 4000 A10A1150 3000 A11A2150 5000 A21A1151 4000 A11A2151 6000 A21A1152 3000 A11Not Pair152 7000 Not Pair1A2152 5000 A21A1153 3000 A11A1153 4000 A11A2153 5000 A21A21999 5000 A21A21999 6000 A21A11999 3000 A11A1155 4000 A11A2155 6000 A21A2156 5000 A21A1156 4000 A11Not Pair157 7000 Not Pair1Not Pair157 8000 Not Pair1A1154 3000 A11A2154 5000 A21A1154 4000 A11A2155 5000 A21A1155 4000 A10Not Pair155 7000 Not Pair1A2156 5000 A20A1156 4000 A10Not Pair156 7000 Not Pair1Not Pair156 8000 Not Pair1A1198 3000 A11A1198 3000 A10A1198 3000 A10A1198 3000 A10A1198 3000 A10A1200 4000 A11A1200 4000 A10A1200 4000 A10A1200 4000 A10A1200 4000 A10A1200 4000 A10A1198 4000 A11A1198 4000 A10A1198 4000 A10A1198 4000 A10A1198 4000 A10

Saxon10 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

Saxon10

Post Prodigy

4 years ago

Solved

Unique count based on the multiple column with DAX

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

Need Help

ryan_mayu
4 years ago
Saxon10

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
unique.pbix20 KB

Forum Discussion

Unique count based on the multiple column with DAX

3 Replies

Related content

Sequential Order column based on multiple criteria

Count unique/distinct values over multiple columns

Unique number column

Multiple line and clustered columns chart

Unique Count in Row convert