cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bergfuchs
New Member

DAX Funktion returns table with a calculated column

Hello!

 

I am struggling to find the right dax function for one specific problem:

 

I would like to write a DAX expression that returns a table:

Lets say I have a Master Data Table with all Materials.

I also hav a Transaction Table with all the Purchase Orders.

 

I would like to get a table with

Column1: Matrial ID

Column2: Number of Purchase Orders

 

It shoud consider all the currently active filters. So if the year 2015 is filtered it should only contain Materials which have Purchase orders in 2015.

 

I think basically the Summarize Function would do it:

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)  

 but actually i want to summarize any rows, but just want to get a filtered table with one or more calculated columns.

 

I couldnt find the right function for this. Any hints?

Thank you!

1 ACCEPTED SOLUTION

Hello! Thanks for all the proposals,

I actually wanted to calculate a table that i can tehn further use as an argument in another table.

In the meantime I found a solution:

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)  

 

 

View solution in original post

4 REPLIES 4
v-ljerr-msft
Microsoft
Microsoft

@bergfuchs


It shoud consider all the currently active filters. So if the year 2015 is filtered it should only contain Materials which have Purchase orders in 2015.


It is not possible to create a calculate table whose value changes depends on the filter selection on the report. The value of a created calculate table only updates during a dataset refresh. 

 

In your scenario, you should use Table visual to show the Matrial ID and the Number of Purchase Orders on the report as Matt mentioned above. See my sample below.

 

I have a Product table with all products and a Sales table with all the orders like below.

Product

product.PNG

Sales

sales.PNG

1. Use the formula below to create a measure to calculate the number of orders.

NumberOfOrders = COUNTA ( Sales[ProductID] )

2. Show the ProductID along with the created measure in a Table visual on the report.

r2.PNG

Regards

Hello! Thanks for all the proposals,

I actually wanted to calculate a table that i can tehn further use as an argument in another table.

In the meantime I found a solution:

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)  

 

 

View solution in original post

I think the key point to you question is that you want to respect the filters (presumably applied to your report). If this is the scenario, then you can't use a table query function like summarize.  I suggest you add a table to the report and place material iD and a measure Sum of Purchase Orders to the table. This should give you what you need I think. 

 

Or have I missed something?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
BhaveshPatel
Community Champion
Community Champion

Please refer this blog for creating a solution of your problem.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors