cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prime
Microsoft
Microsoft

Power BI Desktop - Add n no. of columns to table dynamically

Hi Team,

 

We have 2 tables in the system as below :

Table 1 : Master table for ‘Purpose’                         

 
Purpose
P1
P2
P3
P4

 

Table 2 : Data containing different purpose applicable for a worker for each fiscal and quarter

PurposeCreatingWorkerFiscalQuarter
P1W12021Q1
P2W12021Q1
P3W12021Q1
P1W12021Q2
P2W12021Q2
P3W12021Q2
P1W22021Q1
P2W22021Q1
P3W22021Q1
P4W22021Q1
P2W32021Q1
P3W32021Q1
P4W32021Q1

 

We have a requirement where we need to show the count of each purpose for the worker, fiscal year, quarter combination.

 
CreatingWorkerFiscalQuarterP1P2P3P4
W12021Q1111 
W12021Q2111 
W22021Q11111
W32021Q1 111


The above needs to be done alongside other calculated measures for the combination – worker, fiscal year, quarter. Our end result needs to look like below. The columns Blue are calculated measures, the columns Red are the various Purpose in the system and the count for this can grow.

 

 


Capture.PNG


ASK-

  1. We are looking for suggestions on using the correct visualisation that helps us achieve the above result.
  2. Is there a way where we can dynamically add columns to the table based on the number of records in Table 1 (Purpose master) above? How can we achieve it?
  3. Any other suggestions.

 

1 REPLY 1
amitchandak
Super User
Super User

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!