Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
6 years ago
Solved

How to link rows together in a same table?

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 th...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

jereaallikko's avatar
jereaallikko
Helper III
6 years ago

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's avatar
Icey
Community Support
6 years ago

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.