cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jayjay99
Frequent Visitor

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:

CityProduct AProduct BProduct CProduct D
New York2356
Florida4545
Toronto5261
Boston7143

 


The expected result in DAX expression:

Product TypeTotal Sales
Product A18
Product B11
Product C19
Product D15



I don't mind writing hardcoded values for the Product. Basically, I would like to write some DAX expression like

Create new table

 > Add row ( "Product A", Sum(ProductA),

 > Add row ( "Product B", Sum(ProductB)

Return Table.

I don't want to create a separate calculated table as it doesn't sync with the main dataset on using a filter so looking to created Dax expression which will return the table and also remains in sync with the main dataset so whenever the filters are changed my values in the Dax table also changes.

Thanks in advance for the help.

 

9 REPLIES 9
AnkitKukreja
Super User
Super User

Hi @jayjay99 

 

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.

 

Query Editor, unpivot.JPG

Table.JPG

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

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

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

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 ?

Hi @jayjay99 

 

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

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

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

Hi @jayjay99 

 

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

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

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.

Hi @jayjay99 

But, unpivot would be added as a step in your query editor and it would update as your data is updated everytime. Just thinking it out loud, if you might have missed that point.
Anyways, thanks and good luck.

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors