Creating a ("derived") table w/atributes and aggregation func

[vyogi]

I'm a database developer relatively new to PowerBI and the extensive DAX library. Im trying to understand whether or not I can generate a "derived table" from a table visualization I created. For example, I have a table viz with the following attributes and aggregation function - RanchNumber (tbl 1), Cycle (tbl2), Account (tbl3), and Amount (tbl4, SUM() aggregation function). These tables are all related. I have a similar data set from 3 tables and 2 aggregate functions (MAX() and MIN()).

I want to extend these data sets to their own tables for reference.

Ive been researching the issue but with more simplified scenarios using SUMMARIZE, SUMMARIZECOLUMNS - Im unclear about the aggregation part - not sure if these functions are the wrong direction or if I need nested functions. If nested, how to leverage the relationship?

Much gratitude for guidance in advanced.

[Icey]

Hi @vyogi ,

It would be great if you could tell me what you wanted in a concrete example.

Best Regards,

Icey

[vyogi]

Hi @Icey, thank you for the response. Sure, here are a couple example of table visualizations Im referring to - Id like to create tables featuring these attributes and aggregations in power bi, the application would be to leverage such tables for lookup and budget analysis.

[Icey]

Hi @vyogi ,

Do you mean that you want to convert your table visualizations to calculated tables?

If so, please give me some sample data and your measures. 

Best Regards,

Icey

[vyogi]

Hi @Icey , Yes, Id like to convert the visualizations to tables; I suppose this would be a "calculated table" which is a new concept for me.

Example of data for first table is Ranch.RanchNumber, Cycle.CycleDesc, Max Date (measure), Min Date (Measure). Measures are simple and straight forward: Max Date = MAX(Production[week ending]) and Min Date = Min(Production[week ending])

Second table is Ranch.RanchNumber, Account.AccountDesc and Account.Amount. No measures.

[Icey · Accepted solution]

Hi @vyogi ,

1. If you want to sellect different columns and measures from different tables to generate a new calculated table, you can use SUMMARIZE or SUMMARIZECOLUMNS. And this post may be a bit helpful too.

2. If you want to add calculated columns to the given table or table expression, you can use ADDCOLUMNS.

3. If you want to create a table with the Cartesian product, you can use GENERATE.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vyogi]

Thank you for the responses and help Icey