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 IV
Super User IV

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

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors