Forum Discussion
Dynamically create measure based table
I have a datamodel with 5 tables- each having 2 measures M1 & M2
All M1 measures for each of these tables relate to Tasks- so i want to call the row as "Tasks"
All M2 measures for each of these tables relate to Travelers- so i want to call this row as "Travelers"
Those 10 measures are M1T1, M1T2, M1T3..............M2T5 as shown in some cells in a sample image below
I want to create a 6th table "Table 6" which would use these 10 measures in each of the cells highlighted below to show me something as this in data mode:
Table 1 Table 2 Table 3 Table 4 Table 5
Measure 1- Tasks M1T1 M1T2 M1T3 1529 836
Measure 2- Travelers M2T1 200 1200 1200 M2T5
I need to create this 6th table with this view having multiple measures because i want to use a visualization using the values in this table.
Currently, i cannot use any visualization that would show me a graph based on 10 different measures clubbing them in 2 categories (Tasks, Travelers) without thinking of this 6th table.
Anonymous you can create a table using summarize function and then use UNION to append these tables together, something like dax expression given below
Table6 = UNION ( SUMMARIZE ( Table1, Table1[Id], "Name", "Table1", "Tasks", [Task Measure], "Travelers", [Traveles Measure] ), SUMMARIZE ( Table2, Table2[Id], "Name", "Table2", "Tasks", [Task Measure], "Travelers", [Traveles Measure] ), SUMMARIZE ( Table3, Table3[Id], "Name", "Table3", "Tasks", [Task Measure], "Travelers", [Traveles Measure] ) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Anonymous solution attached, the similar thing you can do in power query instead of Dax
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
7 Replies
Anonymous you can create a table using summarize function and then use UNION to append these tables together, something like dax expression given below
Table6 = UNION ( SUMMARIZE ( Table1, Table1[Id], "Name", "Table1", "Tasks", [Task Measure], "Travelers", [Traveles Measure] ), SUMMARIZE ( Table2, Table2[Id], "Name", "Table2", "Tasks", [Task Measure], "Travelers", [Traveles Measure] ), SUMMARIZE ( Table3, Table3[Id], "Name", "Table3", "Tasks", [Task Measure], "Travelers", [Traveles Measure] ) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hi parry2k , i was trying to create it- but i may need a bit more help- please suggest:-
What should be referenced for Table1[Id], Table2[Id]......?
Also, these Task Measures and Traveler Measure would be unique like M1T1 for Table 1, Taks Measure and M2T5 for Table 5 Traveler Measure?
Anonymous table 1 , 2, 3 refers to the name of your tables. Id is just an example column. whatever common columns you want from these tables, you will list all there.
Hi,
I am not clear with your question. Share some data and show the expected result.
