DAX Funktion returns table with a calculated column

[bergfuchs]

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!

[v-ljerr-msft]

@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

Sales

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.

Regards

[bergfuchs · 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>]…)  

[MattAllington]

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?

[BhaveshPatel]

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