Forum Discussion
Showing Two Data fields in One Column of Table visual using Measure
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
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!
4 Replies
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 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!
