tables in dashboard

[luis_pflucker]

Hi All,

based on Issues and Execution excel tables (as attached), I wanted to create a Dasboard table using Power BI (model also in the attached).

In Execution table are showed Folders, Scenarios and Cases.

In Issues table are showed Folders, Scenarios, Cases and Issues columns, where one Issue could be linked to one or more Cases.

The idea is tha in Dashboard table are showed ONLY Folders, Scenarios (NO showed Cases), also Issues are showed in different columns

About Status Scenario column, the conditions are:

if Progress = 100%, then Completed,

else = 0% Not Started,

else blocked by one issue then unblocked,

else blocked by 2 or more issues then blocked.

I would appreciatte your support/suggestions.

Luis

Execution

Folder	Scenario	Cases
Folder1	SCN11	CASE111
Folder1	SCN11	CASE112
Folder1	SCN11	CASE113
Folder1	SCN12	CASE121
Folder1	SCN12	CASE122
Folder1	SCN12	CASE123
Folder1	SCN12	CASE124
Folder1	SCN13	CASE131
Folder1	SCN13	CASE132
Folder1	SCN13	CASE133
Folder1	SCN14	CASE141
Folder1	SCN14	CASE142
Folder1	SCN14	CASE143
Folder2	SCN21	CASE211
Folder2	SCN21	CASE212
Folder2	SCN21	CASE213
Folder2	SCN22	CASE221
Folder2	SCN22	CASE222
Folder2	SCN22	CASE223
Folder2	SCN23	CASE231
Folder2	SCN23	CASE232
Folder2	SCN23	CASE233
Folder2	SCN23	CASE234
Folder2	SCN24	CASE241
Folder2	SCN24	CASE241

Issues

Folder	Scenario	Cases	Issues
Folder1	SCN11	CASE111	Issue1
Folder1	SCN12	CASE121	Issue2
Folder1	SCN12	CASE123	Issue1
Folder1	SCN12	CASE124	Issue5
Folder1	SCN13	CASE133	Issue5
Folder1	SCN14	CASE141	Issue2
Folder1	SCN15	CASE153	Issue3
Folder2	SCN21	CASE212	Issue1
Folder2	SCN21	CASE212	Issue4
Folder2	SCN23	CASE231	Issue3
Folder2	SCN23	CASE233	Issue4
Folder2	SCN24	CASE241	Issue3

Dashboard

Folder	Scenario	Progress	Issues	Issues	Issues	Status Scenario
Folder1	SCN11	100%	Issue1			Completed
Folder1	SCN12	25%	Issue1	Issue5	Issue2	Blocked
Folder1	SCN13	10%	Issue5			Unblocked
Folder1	SCN14	10%	Issue2			Unblocked
Folder1	SCN15	20%	Issue3			Unblocked
Folder2	SCN21	30%	Issue1	Issue4		Blocked
Folder2	SCN22	0%				Not Started
Folder2	SCN23	45%	Issue4	Issue3		Blocked
Folder2	SCN24	5%	Issue3			Unblocked

[lbendlin]

Sure but this will break down the moment you have four issues in a scenario. The "bad design" smell is strong here.

[lbendlin]

You cannot create dynamic columns in DAX. Columns are computed only once during dataset refresh.

It is possible to emulate this with measures, but it is an enormous effort for not much return. https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...

[luis_pflucker]

Yes, too many steps, but is there anyway to create columns manually and store per each column using any DAX Calculation (do not know what DAX) the values, see below, there are 3 columns added manually and and each row is included the value, if no there is no value, then cell is empty. 

older	Scenario	Progress	Issues1	Issues2	Issues3	Status Scenario
Folder1	SCN11	100%	Issue1			Completed
Folder1	SCN12	25%	Issue1	Issue5	Issue2	Blocked
Folder1	SCN13	10%	Issue5			Unblocked
Folder1	SCN14	10%	Issue2			Unblocked
Folder1	SCN15	20%	Issue3			Unblocked
Folder2	SCN21	30%	Issue1	Issue4		Blocked
Folder2	SCN22	0%				Not Started
Folder2	SCN23	45%	Issue4	Issue3		Blocked
Folder2	SCN24	5%	Issue3			Unblocked

[lbendlin]

I assume the last line in your Execution table is a typo? Should say "CASE242", right?

And in the Issues table the row 

Folder1

SCN15

CASE153

Issue3

has no match in the Execution table. What should be done in such a scenario?

Anyway, here are your measures:

Issues = CONCATENATEX(Issues,Issues[Issues],"")
Status Scenario = SWITCH(TRUE(),count(Issues[Issues])=1,"Unblocked",count(Issues[Issues])>1,"Blocked","Not Started")

[luis_pflucker]

Hi, thanks for your feedback. Firstable I solved Scenario Status counting using COUNTA DAX, but also used your suggestion using Switch instead of IF DAX, it seems switch is more powerful than IF.

About CONCATENATEX is good suggestion, but not solving at the end, because I am trying to have in different columns the Issues separately per each row, for instance in Folder 1, SCN12 I wanted to have 3 columns (one column for Issue1, other columnd for Issue2 and other for Issue5), same for Folder 2, SCN21 should get 2 different column (one column for Issue1 and other column for Issue4). It means as much Issue columns as quantities of Issues per each row.

Please, could you suggest?

regards 

[luis_pflucker]

Hi, 

you are right!!!,: both my mystakes, below I have updated Execution table:

[lbendlin]

Where does the Progress column come from?  It is not mentioned in your original description.

What is the relationship of cases to folders and scenarios? Is it a strict hierarchy, or can a case  be assigned to multiple scenarios etc?

[luis_pflucker]

Hi,

% of progress column can be different,s, however I put 100% and 0% to see in case completed or not started.

 Relationship of cases to folders and scenarios are strict hierarchy based.

regards