Forum Discussion
Dynamic Table in DAX expression
Super UserHi
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
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 ?
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
- AnkitKukreja
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
Calculate table will have an issue of static data. Because it won't have dynamic data. Unpivot is also a one-time solution and the same challenge of static data. Thanks, Ankit for looking into though.
- AnkitKukreja
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