How to link rows together in a same table?

[jereaallikko]

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

[jereaallikko]

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

[Icey · Accepted 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.

[Jimmy801]

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

[Anonymous]

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.

[Greg_Deckler]

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