Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic Table in DAX expression

Hello All, I have the below dataset of products and store information. I would like to have a simple DAX Expression which returns Product and total sales. Main Dataset: City Product A ...
DAX
Table
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Ankit,

 

Thanks for replying. In my case the unpivot option won't work so looking for a measure option here which returns the product and it's total. Will be great if you can tell me the DAX expression for the same.

 

Thanks,

Jay

AnkitKukreja's avatar
AnkitKukreja
Icon for Super User rankSuper User
5 years ago

Hi

Not sure what you were looking for exactly, but unpivot would be the best approach considering the scenario you have shared here. But, if you've some other requirements, then please use below dax for getting the same results.

Sales by Product =

UNION(

SELECTCOLUMNS( 'Table (2)' , "Product A" , "Product A" , "Sales" , 'Table (2)'[Product A] ),

SELECTCOLUMNS( 'Table (2)' , "Product B" , "Product B" , "Sales" , 'Table (2)'[Product B] ),

SELECTCOLUMNS( 'Table (2)' , "Product C" , "Product C" , "Sales" , 'Table (2)'[Product C] ),

SELECTCOLUMNS( 'Table (2)' , "Product D" , "Product D" , "Sales" , 'Table (2)'[Product D] )

)


Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Ankit,

    I tried above formula and it gave me the error viz. The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

    Did you tried this dax in your test pbix ?IF so, can you please upload the sample pbix ?

      • AnkitKukreja's avatar
        AnkitKukreja
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous 

         

        I believe calculate table or unpivot is the only option according to me.

        Let's see if we can get any other solution.

         

        Thanks,

        Ankit

    • AnkitKukreja's avatar
      AnkitKukreja
      Icon for Super User rankSuper User
      5 years ago

      Hi Anonymous 

       

      You're right this will not work in measure, i've used this as a Calculated table.
      I missed you're point that you don't need a calculated table. So I'll let other folks provide you the solution, as I'm not aware about the same.

      Will post my solution, when I'll get to it.

       

      Thanks

      Ankit