Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.)
Solved! Go to Solution.
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!
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!
I can not thank you enough!
You helped me sooooooooooo much.
I really, really appreciate your time and attention.
You save my week.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |