locked
Calculating same duplicated fields value on DAX RRS feed

  • 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

    Thank you in advance !


    erkindunya

    Tuesday, March 6, 2012 11:34 AM

Answers

  • 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
    3 Food Box Caddy
    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 erkindunya2010 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,

    your formula

    =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