Dynamically create measure based table

[Anonymous]

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.

[parry2k · Accepted solution]

@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.⚡

[Ashish_Mathur]

Hi,

I am not clear with your question.  Share some data and show the expected result.

[parry2k · Accepted solution]

@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]

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?

[parry2k]

@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.

[Anonymous]

@parry2k There are no common columns- i think this is what would be needed to group them right?

Unfortunately, these table do not relate to one another

I'll share more to be clear- please see if that helps

	Table 1	Table 2	Table 3	Table 4	Table 5
Tasks	12371	315	1574	1529	836
Travelers	7514	200	1200	1200	800

In the table above- i have all 10 measures giving me values as is highlighted in orange.

But i don't know if Summarize would work since i don not have a common column- i just want these measures to appear as shown above in a table so that later i can use them in some visualization.

@parry2k - Is there a way i can use any visual would would take in all these 10 measures? I want to show something like this 

[Anonymous]

Hi @parry2k , @Ashish_Mathur 

https://drive.google.com/file/d/1IW3idw1azJ2-1wf2Z-HXy610hKlsmorX/view?usp=sharing

I created dummy data which replicates my requirement in this PBIX file attached.

So, this PBIX file has 5 tables- each has 2 columns- Tasks and Travelers. The values in any of the tables do not relate to the value in another table.

In Excel- my data would look like below- Tasks as Count & Travelers as Distinct Count

	Table1	Table2	Table3	Table4	Table5
TASKS (COUNT)	100	20	30	40	50
TRAVELERS (DISTINCT COUNT)	50	10	15	20	25

I want to create a visual out of these 5 tables as below:- 

[parry2k · Accepted solution]

@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.⚡