Remove Duplicate Rows From A Filtered Table

[LFrench]

I have a table that displays a list of courses that a student needs to take, based on the qualification they are hoping to achieve. Each qualification has three levels, essentially an entry-level, supervisor and manager level. A text filter allows you to sort by any one qualification + level without a problem, and it displays a list of courses required. Now, we want to add, if you want to be a supervisor, you can add the entry level + the supervisor level to the text filter and get all of the required courses for both requirements in the same table (because being a supervisor requires you completed the entry level courses first).

Now, the hard part. We want to elliminate the duplicates if both levels (or all three) require the same courses, they should only display once, ideally at the lowest level required (Entry Level, Supervisor, Manager). So, I can't elliminate the duplicates from the PowerQuery Table, because I need them to show up if you select only the one level, but I don't want two of them if you select multiple levels. 

Example of what it does now:

Course Name	Qualifiation For	Level Required For
Course 1	Qualification 1	Entry Level
Course 1	Qualification 1	Supervisor
Course 2	Qualification 1	Entry Level
Course 3	Qualificiation 1	Supervisor

What I am looking for: 

Course Name	Qualification For	Level Required For
Course 1	Qualification 1	Entry Level
Course 2	Qualification 2	Entry Level
Course 3	Qualification 3	Supervisor

[v-priyankata]

Hi @LFrench 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[DataNinja777]

Hi @LFrench ,

You can solve this problem using DAX by creating a sort order for the different levels, identifying the lowest level required for each course, and then filtering your visual to only show that row. First, add a calculated column to your table to assign a numeric value to each level. For example:

LevelSort = 
SWITCH(
    [Level Required For],
    "Entry Level", 1,
    "Supervisor", 2,
    "Manager", 3,
    99
)

Then, create a measure that calculates the minimum level for each combination of Course Name and Qualification, ignoring any filters on the level. This measure will look like this:

MinLevelSort =
CALCULATE(
    MIN('Courses'[LevelSort]),
    ALLEXCEPT('Courses', 'Courses'[Course Name], 'Courses'[Qualification For])
)

Next, create another calculated column that checks if the current row is the one with the minimum level. If it is, it returns 1; otherwise, it returns 0. This column can be defined as follows:

ShowRow =
IF(
    'Courses'[LevelSort] = [MinLevelSort],
    1,
    0
)

Finally, in your table visual, apply a filter where ShowRow equals 1. This way, when you filter for multiple levels (e.g., Entry Level and Supervisor), only the row corresponding to the lowest required level for each course will be displayed, eliminating the duplicate appearance of the same course across multiple levels.

Best regards,

[rohit1991 · Accepted solution]

Hi @LFrench ,

Since removing duplicates directly in Power Query would impact single-level selections, the best approach is to handle this in DAX using a calculated table or measure that evaluates the context of selected levels. You can create a calculated table or use a SUMMARIZE combined with MINX or RANKX to group by course and qualification, then return only the row with the lowest "Level Required For" based on a custom sort order (e.g., Entry Level = 1, Supervisor = 2, Manager = 3). 

By applying that logic, when multiple levels are selected, each course is shown only once at its lowest required level. This preserves full detail when only one level is selected, but avoids duplicate rows when stacking qualifications.

First, add a new column to your table to rank the levels numerically so we can compare them easily:

LevelRank = 
SWITCH(
    [Level Required For],
    "Entry Level", 1,
    "Supervisor", 2,
    "Manager", 3,
    999  -- fallback for unexpected values
)

Step 2: Create a Summary Table (Dynamic View)

CourseSummary =
VAR SelectedLevels = VALUES('YourTable'[Level Required For])
RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER('YourTable', 'YourTable'[Level Required For] IN SelectedLevels),
            'YourTable'[Course Name],
            'YourTable'[Qualification For]
        ),
        "MinLevel", 
            CALCULATE(
                MIN('YourTable'[Level Required For]),
                FILTER(
                    'YourTable',
                    'YourTable'[Course Name] = EARLIER('YourTable'[Course Name]) &&
                    'YourTable'[Qualification For] = EARLIER('YourTable'[Qualification For]) &&
                    'YourTable'[Level Required For] IN SelectedLevels
                )))

This will summarize the data, remove duplicates, and return each course at the lowest applicable level based on the user’s slicer/filter selection.