CODEWITHSUNDEEP
powerbidax
Breno Michaelsen
Breno Michaelsen

Reputation: 35

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

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.

Upvotes: 1

Views: 195

Answers (2)

Mik
Mik

Reputation: 2103

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])
    )
)

Upvotes: 1

Marcus
Marcus

Reputation: 3995

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!

Upvotes: 0

Related Questions