Forum Discussion
Dynamic Table in DAX expression
Super UserHi Anonymous
This seems quite straight forward, please go into query editor and select your product columns and unpivot them,
you'll get a desired result and for this you might not require dax as well, just drag the columns and it would sum up your data or you can use measure as well.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
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
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-kukreja1904Hi 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 ?Select columns will return only one column so this won't work in this situation https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax