Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prime
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.