Filter Column in SUMMARIZECOLUMNS

[EpicTriffid]

Hi there,

I'm trying to create a list of unique permuations of Unit Code, Programme Code, Unit Academic Year, and Unit Department. This is being pulled from a huge list, so I'm using SUMMARIZECOLUMNS to achieve it. I'm also pulling in a few extra columns of Unit Title and Programme Title.

Now, I'm trying to filter out anything where the Programme Title or the Unit Title is blank. Running the filter on Programme Title works fine, but Unit Title always leaves at least one row with a blank Unit Title, and I can't figure out why. Here is my code:

Linkage Table = 
SUMMARIZECOLUMNS(
    Tbl_Linked_Curriculum[Unit Code],
    Tbl_Linked_Curriculum[Programme Code],
    Tbl_Linked_Curriculum[Unit Academic Year],
    Tbl_Linked_Curriculum[Unit Department],
    FILTER(
        Tbl_Linked_Curriculum,
        AND(Tbl_Linked_Curriculum[Unit Faculty] = "Arts and Humanities", Tbl_Linked_Curriculum[Unit Academic Year] = "23/24") &&
        OR(Tbl_Linked_Curriculum[Course Status (Study Level)] <> "PGR", Tbl_Linked_Curriculum[Course Status (Study Level)] <> "EXTERNAL") &&
        OR(Tbl_Linked_Curriculum[Unit Title] <> BLANK(), Tbl_Linked_Curriculum[Programme Title] <> BLANK())
    ),
    
    "Unit Title", UPPER(TRIM(SELECTEDVALUE(Tbl_Linked_Curriculum[Unit Title]))),
    "Programme Title", UPPER(TRIM(SELECTEDVALUE( Tbl_Linked_Curriculum[Programme Title])))
)

And this is what I get back when I filter to any remaining blanks in Unit Title:

(Excuse the awful redactions)

Now, i assumed for a while that it was because the Unit Title and Programme Title fields aren't included in the grouping columns, so that might be affecting what can be filtered, but all the other filters seem to apply and work. I've also used [Unit Title] <> blank(), [Unit Title] <> "", NOT(ISBLANK()), all of that, and nothing. 

Am I missing something fundamental here?

[v-tangjie-msft]

Hi @EpicTriffid ,

Please try this :

Linkage Table = 
SUMMARIZECOLUMNS(
    Tbl_Linked_Curriculum[Unit Code],
    Tbl_Linked_Curriculum[Programme Code],
    Tbl_Linked_Curriculum[Unit Academic Year],
    Tbl_Linked_Curriculum[Unit Department],
    FILTER(
        Tbl_Linked_Curriculum,
        AND(Tbl_Linked_Curriculum[Unit Title] <> BLANK(), Tbl_Linked_Curriculum[Programme Title] <> BLANK()) &&
        AND(Tbl_Linked_Curriculum[Unit Faculty] = "Arts and Humanities", Tbl_Linked_Curriculum[Unit Academic Year] = "23/24") &&
        OR(Tbl_Linked_Curriculum[Course Status (Study Level)] <> "PGR", Tbl_Linked_Curriculum[Course Status (Study Level)] <> "EXTERNAL") 
 ),
    
    "Unit Title", UPPER(TRIM(SELECTEDVALUE(Tbl_Linked_Curriculum[Unit Title]))),
    "Programme Title", UPPER(TRIM(SELECTEDVALUE( Tbl_Linked_Curriculum[Programme Title])))
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[lukiz84]

        OR(Tbl_Linked_Curriculum[Unit Title] <> BLANK(), Tbl_Linked_Curriculum[Programme Title] <> BLANK())

This part gives you a blank unit title row when programme title <> BLANK()

[EpicTriffid]

Thanks for the reply @lukiz84 ! 

I just removed the Programme Title filter, and the OR function, but i still get the same results?