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

@prime , Seem like hybrid display

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!