Returning rows with a filter set

[patrickoleary85]

Hi - I have a table that contains 4 columns, where rows are returned when the value of [Hours} is greater than or equal to the value in [Goal]. To do this, I divide Hours by Goal and set the rows to return if the value in [Column] is greater than or equal to 1.0. 

I want to ONLY show ONE row per value in the [Region] column, the highest. 

For example, below, I want to ONLY show the Arizona row with [Hours] = 610.64; and ONLY show the Broward row with [Hours] = 3689.44. I want to show the row with the highest number of Hours for each [Region].

The values in [Hours] are a sum of the hours for that specific week. 

Can anyone help me figure this out? I've tried a few measures with MAX but it's not giving me the sum, nor is it giving me only one row. 

Thank you in advance

[PhilipTreacy · Accepted solution]

Hi @patrickoleary85 

Download example PBIX file

You mention that you've tried a few measures (which are DAX) but you've posted in the Power Query forum so I'm assuming you want a PQ solution.

One way to do this is to Group the rows on the Region using the MAX of the Goal Met column (here's my sample data)

Merge the query with itself using the table resulting from the step where you create the Goal Met column.  In my cse this step is called #"Added Custom"

= Table.NestedJoin(#"Added Custom", {"Goal Met"}, #"Grouped Rows", {"Goal Met"}, "Grouped Rows", JoinKind.LeftOuter)

This gives you a column of tables, where there is only data in the table if that row is the max Hours for that Region.

Expand the tables

Filter out the nulls and then delete the last column as it is a duplicate of the Goal Met column

Here's the query in full but you can see this in my example file (linked above)

Regards

Phil

[v-yanjiang-msft · Accepted solution]

Hi @patrickoleary85 ,

According to your description "The values in [Hours] are a sum of the hours for that specific week", I think the snapshot you provided is only a visual but not a table data. there's still a date column in which some have the same Week Ending and seperate Hours, then after put the Week Ending column in a visual, the Hours will add up.

Here's my solution, create a measure.

Check =
VAR _T =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Region],
        'Table'[Week Ending],
        "Hours", SUM ( 'Table'[Hours] )
    )
RETURN
    IF (
        SUM ( 'Table'[Hours] )
            = MAXX ( FILTER ( _T, [Region] = MAX ( 'Table'[Region] ) ), [Hours] ),
        1,
        0
    )

Then put the measure in the visual filter and set to 1

After apply filter, get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[patrickoleary85]

This worked perfectly, thank you!

[PhilipTreacy · Accepted solution]

Hi @patrickoleary85 

Download example PBIX file

You mention that you've tried a few measures (which are DAX) but you've posted in the Power Query forum so I'm assuming you want a PQ solution.

One way to do this is to Group the rows on the Region using the MAX of the Goal Met column (here's my sample data)

Merge the query with itself using the table resulting from the step where you create the Goal Met column.  In my cse this step is called #"Added Custom"

= Table.NestedJoin(#"Added Custom", {"Goal Met"}, #"Grouped Rows", {"Goal Met"}, "Grouped Rows", JoinKind.LeftOuter)

This gives you a column of tables, where there is only data in the table if that row is the max Hours for that Region.

Expand the tables

Filter out the nulls and then delete the last column as it is a duplicate of the Goal Met column

Here's the query in full but you can see this in my example file (linked above)

Regards

Phil

[patrickoleary85]

I attempted this as well, and it worked great. I appreciate it!

[PhilipTreacy]

Hi @patrickoleary85 

Glad that my PQ solution worked too.  Please mark my answer as another solution so that anyone else reading this knows how to solve the problem using Power Query.  This is the PQ forum after all!  🙂

Regards

Phil