Forum Discussion
Bring the same column with conditions
Hi! I'm new on Power BI, so I apologize in advance if my question is stupid, but I've been looking here at this forum, and I couldn't find a solution
I have 3 tables. One table of devices, one of owners, and a third one that connects both called Device_Owner(DeviceID, OwnerID and OwnerSeq).
One device has first and second owner. That is defined on the database by the table Device_Owner, column OwnerSeq. If the OwnerSeq = 1, then is the first.
Now I need to bring that in table with columns like DeviceName, FirstOwner and SecondOwner.
(I tried to create a new table with DeviceID, Owner, and OwnerSeq, but I couldn't.)
1) If you want this in a New Table (not a Table Visual)
click New Table on the Modeling Tab and type this
Summary Table = SUMMARIZECOLUMNS ( Devices[DeviceName], "First Owner", CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 1 ) ), "Second Owner", CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 2 ) ) )2) You can also achieve this without creating a New Table by creating these 2 Measures instead
First Owner = CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 1 ) )and
Second Owner = CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 2 )
)then click the Table Visual icon in the Visualizations area
add the DeviceName from the Devices Table and the 2 Measures
Hope this helps! :smileyhappy:
2 Replies
SeanCommunity Champion
1) If you want this in a New Table (not a Table Visual)
click New Table on the Modeling Tab and type this
Summary Table = SUMMARIZECOLUMNS ( Devices[DeviceName], "First Owner", CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 1 ) ), "Second Owner", CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 2 ) ) )2) You can also achieve this without creating a New Table by creating these 2 Measures instead
First Owner = CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 1 ) )and
Second Owner = CALCULATE ( FIRSTNONBLANK ( Owners[OwnerName], 1 ), FILTER ( Device_Owner, Device_Owner[OwnerSeq] = 2 )
)then click the Table Visual icon in the Visualizations area
add the DeviceName from the Devices Table and the 2 Measures
Hope this helps! :smileyhappy: