# Calculating same duplicated fields value on DAX

• ### Question

• Hi All,

I got a problem on DAX, the below is sample table I have 7 table just duplicated from the first one and the value has 7 values as well same values (value 1 to value 7) most cases they might be blank, the big questions how can I calculate how many items in any particular row? I planing to use DAX "CONCATENATE" function when I use this function the result for row one will be "value 1,value 3,value 3,value 4,value 5,value 7", I need know how many Value 1 ? Value 2 ? Value 3 ? Value 4 ? Value 5 ?

Value 6 ? Value 7 ? on any row for example in row one  "Value 1 =1 Value 2 =0  Value 3= 2  Value 4 =0  Value 5 =1 Value 6 =0  Value 7 =1"

 ********************TABLE*********************

 Data 1 Data 2 Data 3 Data 4 Data 5 Data 6 Data 7 value 1 value 3 value 3 value 4 value 5 value 7 value 2 value 4 value 5 value 3 value 1 value 2 value 1 value 1 value 1 value 4 value 1 value 4 value 4 value 2 value 3 value 5 value 5 value 6 value 4 value 4 value 1 value 1 value 3 value 7 value 1 value 1 value 8 value 4 value 5

erkindunya

Tuesday, March 6, 2012 11:34 AM

• It's pretty easy, you need to build a table containing the distinct values value1...value5, which I called T, then the formula is straightforward:

```=SUMX (
T,
SUMX (
Table,
IF (Table[D1] = T[V], 1, 0) +
IF (Table[D2] = T[V], 1, 0) +
IF (Table[D3] = T[V], 1, 0) +
IF (Table[D4] = T[V], 1, 0)
)
)```
I used 4 columns, you can use as many as you want. Not a top performer, but it should solve the issue. Clearly, to get best performance you will need to modify your data model

Alberto Ferrari
http://www.powerpivotworkshop.com

Tuesday, March 6, 2012 12:51 PM
• Erik,

The formula should be used for a measure, not for a calculated column... just create a measure with the formula, and everything will work fine

Alberto Ferrari
http://www.powerpivotworkshop.com

Wednesday, March 7, 2012 8:58 PM

### All replies

• It's pretty easy, you need to build a table containing the distinct values value1...value5, which I called T, then the formula is straightforward:

```=SUMX (
T,
SUMX (
Table,
IF (Table[D1] = T[V], 1, 0) +
IF (Table[D2] = T[V], 1, 0) +
IF (Table[D3] = T[V], 1, 0) +
IF (Table[D4] = T[V], 1, 0)
)
)```
I used 4 columns, you can use as many as you want. Not a top performer, but it should solve the issue. Clearly, to get best performance you will need to modify your data model

Alberto Ferrari
http://www.powerpivotworkshop.com

Tuesday, March 6, 2012 12:51 PM
• Hi Alberto,

You are legend and fast thank you very much again!

Regards

Erkindunya

erkindunya

Tuesday, March 6, 2012 1:02 PM
• Hi Alberto,

Sorry I am getting an error " The following syntax error occurred during parsing: Invalid token, Line 5, Offset 50, "
= GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 1] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 2] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 3] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 4] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 5] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 6] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 7] = GoodsLookups[Goods Type], 1, 0)

)
).
The calculated column 'Event[CalculatedColumn1]' contains a syntax error. Provide a valid formula."

GoodsLookups table
Goods ID Goods Type
1        Black Box Lid
2        Black Recycle Box
4 Food Waste Bin
5 Green Box
6 Green Box Net
7 Wheeled Rubbish Bin

The formula :

=SUMX (
Goods,
SUMX (
Event,
IF (Event[Goods 1] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 2] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 3] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 4] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 5] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 6] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 7] = GoodsLookups[Goods Type], 1, 0)

)
)

Could you help !

erkindunya

• Edited by Tuesday, March 6, 2012 1:59 PM missing part
Tuesday, March 6, 2012 1:53 PM
• Erki, your formula does not look like mine... anyway, I cannot be of any help in solving a syntax error, the parser works much better than me in finding them. :)

Alberto Ferrari
http://www.powerpivotworkshop.com

Tuesday, March 6, 2012 2:01 PM
• Hi Alberto,

thank you for the reply it is same I added 3 more fields,

=SUMX (

```    T,
SUMX (
Table,
IF (Table[D1] = T[V], 1, 0) +
IF (Table[D2] = T[V], 1, 0) +
IF (Table[D3] = T[V], 1, 0) +
IF (Table[D4] = T[V], 1, 0)
)
)```

my formula

=SUMX (
Goods,
SUMX (
Event,
IF (Event[Goods 1] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 2] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 3] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 4] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 5] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 6] = GoodsLookups[Goods Type], 1, 0) +
IF (Event[Goods 7] = GoodsLookups[Goods Type], 1, 0)

)
)

erkindunya

Tuesday, March 6, 2012 2:14 PM
• Hi ALberto,

I fixed it by error trcking, I am getting all totals for al 7 goods which good news how to find how many indivual items there?

such as goods 1=125, goods 2= 965 etc..?

erkindunya

Tuesday, March 6, 2012 4:35 PM
• That's easy, just put the good on rows, columns or slicers, the formula will work without any change.

Alberto Ferrari
http://www.powerpivotworkshop.com

Wednesday, March 7, 2012 3:29 PM
• Hi Alberto,

Yes it was easy but it only returns Goods 1 column data I tried to swich to next Goods 2 column or other all returns same result.

I uploaded the workbook Click here, if you have time I will very great-full you already helped me a lot when I got problem. I just want to solve the issue completely.

Thank you again and again !

erkindunya

Wednesday, March 7, 2012 6:12 PM
• Erik,

The formula should be used for a measure, not for a calculated column... just create a measure with the formula, and everything will work fine

Alberto Ferrari
http://www.powerpivotworkshop.com

Wednesday, March 7, 2012 8:58 PM
• Hi Alberto,

Thank you very much.

erkindunya

Tuesday, March 13, 2012 11:05 AM