Showing Two Data fields in One Column of Table visual using Measure

[Mann]

Hi Guys,

I have a table with two different columns as per project shift type. One is "Shift Type A" and other is "Shift Type B" as shown:

Ind ID	Shift Date	Shift Type B	Shift Type A	Values
1	10/01/2019	Day Shift		60
1	10/01/2019	Day Shift	Day Shift	160
2	10/02/2019	Day Shift		60
2	10/02/2019	Day Shift	Day Shift	200
3	10/01/2019	Day Shift		180
3	10/01/2019	Day Shift	Day Shift	450
3	10/01/2019	Night Shift		180
3	10/01/2019	Night Shift	Night Shift	540
3	10/02/2019	Day Shift	Day Shift	120
4	10/03/2019	Day Shift		120

Note:

1) Shift Type B can only have two values: "Day Shift" and "Night Shift"

2) Shift Type A can have three values: "Day Shift", "Night Shift" and Blank. 

I have a slicer coming from unrelated table as:

I want final table visual to show only one column for "Shift Type" such that when "A-DS" is selected it should show rows of Shift Type A= "Day Shift" and so on for other values.

One can select multiple values of slicer also. It can be like selecting both Day Shift and Night Shift for Project A only and same way selecting both shift types of project B only. (The above slicer has one slicer on top of it for Project Type)

How can I achieve this?

Please Note: I can't create any calculated table in my model. I am thinking of putting this logic as to show "Shift Type B" always and filter the records of it when "Shift Type A" related slicer is selected through measures.

Please let me know if this can be achieved.

Thanks

[parry2k]

@Mann it is much easier to achieve with proper data table transformation. The question, doing transformation in PQ will work for you as you mentioned you cannot have a new calculated table, of course it will not a new table, but the existing table will be transformed. Let me know before I proceed with the solution.

[Mann]

Hi @parry2k

Thanks for checking on this. Actually this underlying table is huge in my model. So I want to avoid doing further transformation on this in Power query and using calculated table since other solutions might result in increase of row count.

[parry2k · Accepted solution]

@Mann no problem, I create using DAX, solution attached, focus on shift and shift-type tables. There are other sample tables but not relevant to this solution. You can take it from there and extend the way you like.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Mann]

Thanks @parry2k 

This works perfectly as expected!

Thanks'

Mann