Group by multiple columns - no need to sum

[Melodyv]

Here is what I'm working with.  The FNPI and MIS ID are the same in two rows (MIS ID being the client ID and FNPI being the outcome the client worked toward).  This client achieved the outcome, so I need to get this to where it shows a single row with a "1" in the Served column and a "1" in the Achieved column.

I believe what I need to do is to group by two columns, MIS ID and then FNPI.  I need both because a client may be working toward more than one outcome, (ie one MIS ID may be tied to multiple FNPIs).  Then I need to fill up/down in the Achieved Column so that rows with the same MIS ID and FNPI have the same value.  Then I can just remove duplicate rows.  The problem is I can't figure out how to set up the group by.  All the examples I have come across involve summing or aggregating numbers in some way.  I don't need anything to sum - I just need it to show the client was served and the outcome was achieved.  I am fairly new to PowerBI, and am better at working in Power Query than DAX.  Any help is appreciated.

[ovde]

You could create a third column using power query or dax that adds [Served] and [Achieved], then in the group by you can use MAX to keep the highest value for this new column for each [FNPI] and [MIS ID]. 

This new column would represent a status with 1 = "Served" and 2 = "Served and Achieved". If you want to only keep "Served and Achieved" you don't even need group by and could just use a filter and distinct to achieve your desired outcome.

Hope this helps!

[Melodyv]

I am needing to keep all client results, not just those that were served and achieved, so I don't think a filter would work.  I've tried sorting by multiple columns (FNPI, MIS ID, and Achieved) and then using Fill Down, but I get incorrect results.  It fills all the way down without regard to the FNPI or MIS ID columns.  That's why I'm thinking I need to group them somehow, but I can't seem to get it figured out.

[ovde · Accepted solution]

I think you need to do something simular to this Create Row Number for Each Group in Power BI using Power Query - RADACAD

The key point here is that when you use table.filldown, you need to refer to the column that contains your new subtables not to the last step in the query, this will apply the function to the subtables. Next you can expand the subtables and remove any excess columns. 

I think this should produce the results you're after.