Find and return a new column with unique values comparing two table columns in Power BI

Breno Michaelsen 0
Viewed 51

I have 2 tables, one table for projects and other with services performed. This services always use a project number, but some projects aren't used yet.

Example table projects:

Project Number Object
12345 describe one
45146 describe two
10015 describe three

Example table services performed

Location Price Project Number
City 1 100,000 80002
City 2 200,000 12345
City 3 300,000 56874

I want to know (create a column) with the projects that weren't used yet. At this example, I need a column with 2 lines, project 45146 and 10015. How can I do it?

I'm still learning dax and I thought in SEARCH/FIND, but I didn't get what I need.

Thanks for your help.

dax powerbi
2 Answers

The measure Returns True/False values. You can use it as a measure for matrix or as a calculated column.

= 
ISEMPTY(
    EXCEPT(
        CALCULATETABLE(VALUES(Projects[Project Number]))
        ,VALUES(performed[Project Number])
    )
)
userLOTh10

Can’t you just add a new table and use EXCEPT for this?

Table = 
    EXCEPT ( 
        DISTINCT ( 'projects'[Project Number] ) , 
        DISTINCT ( 'services'[Project Number] ) 
    )

Not sure what this is solving, but if you specifically want to calculate this as a new column (/table) then by all means!

usergIl710
Related