Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mann
Resolver III
Resolver III

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 IDShift DateShift Type BShift Type AValues
110/01/2019Day Shift 60
110/01/2019Day ShiftDay Shift160
210/02/2019Day Shift 60
210/02/2019Day ShiftDay Shift200
310/01/2019Day Shift 180
310/01/2019Day ShiftDay Shift450
310/01/2019Night Shift 180
310/01/2019Night ShiftNight Shift540
310/02/2019Day ShiftDay Shift120
410/03/2019Day 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:

Slicer.PNG

 

 

 

 

 

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

1 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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k 

This works perfectly as expected!

 

Thanks'

Mann

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.