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 all,
I need help with linking rows together based on "Priority". I have created an excel table to visualize the problem;
I have four "priority" rows (Floor of the house), and under each row is linked a floor. The total working hours of each floor (primary rows) is formed by sub rows, in this case, rooms.
How can I modify the dataset create a Power BI report in which I can use a slicer for Floors, and it will filter the report with the chosen floor and with its linked rooms?
The issue was not easy to explain, hopefully somebody understood and could provide me some tips. Feel free to ask more details if needed.
Best Regards,
Jere Aallikko
Solved! Go to Solution.
Hi @jereaallikko ,
How about create a column like so:
Floor =
VAR MaxEstHours =
MAXX (
FILTER ( 'Table', [Level ID] = EARLIER ( [Level ID] ) ),
[Estimated Hours]
)
VAR MaxWorkHours =
MAXX ( FILTER ( 'Table', [Level ID] = EARLIER ( [Level ID] ) ), [Worked Hours] )
RETURN
CALCULATE (
FIRSTNONBLANK ( 'Table'[Topic], 0 ),
FILTER (
'Table',
'Table'[Estimated Hours] = MaxEstHours
&& 'Table'[Worked Hours] = MaxWorkHours
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello all,
Thanks for the answers. Let me rephrase it to make it easier;
I have imported the table into Power BI, and added two extra columns to visualize the problem better.
What I am looking for, is to use a slicer based on the floor level (from Topic column). So basically, I would like the slicer to contain Basement, Ground Floor, 2nd Floor & 3rd Floor. And with it, to show the primary topic (floor level) and sub-topics in a report graphs (f.ex. choosing Ground floor from slicer -> Ground Floor, Kitchen, Living Room, Bathroom & Washroom appears in a graph).
The filtering would work with Level ID column in a wanted way, but that is not the column I want to use as a slicer.
My real dataset contains over 200 rows with 70 primary topics and 133 sub-topics and it grows nearly everyday, so is there a simple way to solve the problem?
Thanks,
Jere
Hi @jereaallikko ,
How about create a column like so:
Floor =
VAR MaxEstHours =
MAXX (
FILTER ( 'Table', [Level ID] = EARLIER ( [Level ID] ) ),
[Estimated Hours]
)
VAR MaxWorkHours =
MAXX ( FILTER ( 'Table', [Level ID] = EARLIER ( [Level ID] ) ), [Worked Hours] )
RETURN
CALCULATE (
FIRSTNONBLANK ( 'Table'[Topic], 0 ),
FILTER (
'Table',
'Table'[Estimated Hours] = MaxEstHours
&& 'Table'[Worked Hours] = MaxWorkHours
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @jereaallikko
as I can see in your dataset you have aggregated values, that would destroy your analysis. First step is to delete the aggregated rows. Then you can excract the identifier of each row, that represents the floor. For each step, at least in your dataset provided, you can find a logic. Hopefully these logics can also be applied to your origin dataset. Here a short example how to achive this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvI1MNB1SixOzU3NK1GK1YlWUlBQAAsGl6QWKDhiCjmBhdzdDA0MddOL8kvzUhTScvLzi2AqITKoupHEgNpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Summary = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Summary", type text}}),
DeleteAggregatedRows = Table.SelectRows
(
#"Changed Type",
each Text.Start(_[Summary],3)= " "
),
CreateFloorColumn = Table.AddColumn
(
DeleteAggregatedRows,
"Floor",
each Text.Split(_[Summary],"-"){0},
type text
),
CreateStepColumn = Table.AddColumn
(
CreateFloorColumn,
"Step",
each Text.Split(_[Summary],"-"){1},
type text
)
in
CreateStepColumn
As alternative you could also keep the aggregated rows and add a new column, were you identify them in order you could exclude them in your report
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
you could create a new column, extracting the text preceding "-" for each row from the summary column, that is:
BM00
GF101
and so on ...
At this point, use the table.group function on this new column, thus obtaining a sub-table for each group (floor) (*).
IF ... this idea seems to match your needs, but you don't know how to do it, put a table like the one on the screen but in a form that is easily copied and someone will write the code for you.
(*)
is it possible to grou by in this sense without the auxiliar column, but in this way, I think, is clearer.
@jereaallikko - If I am understanding this correctly, you would want your dataset laid out like this:
Priority | Subtask | Work Place | Estimated Hours | Work Hours | Remaining Hours |
BM-00 New furniture in basement | New sofa ... | ||||
BM-00 New furniture in basement | Fridgerator for beverages, snacks | ||||
GF-101 Renovating specific rooms from the ground floor | GF101 New oven, coffee machine... |
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.