CODEWITHSUNDEEP
powerbidax
Suresh Resh
Suresh Resh

Reputation: 47

Comparing values of type Text from different table in power BI

I would like to compare the two columns contain texts and label it as "1" if it exist in the other table or "0" if it is not.

input:

enter image description here

*Both the tables dont have any kind of relationship

Upvotes: 1

Views: 7120

Answers (2)

Olly
Olly

Reputation: 7891

You could use a measure:

Occur = 
VAR CurrentName = 
    IF ( 
        HASONEVALUE ( 'Table 1'[Name] ),
        VALUES ( 'Table 1'[Name] ),
        BLANK()
    )
RETURN
    CALCULATE ( 
        COUNTROWS ( 'Table 2'),
        FILTER ( 
            'Table 2',
            'Table 2'[Name] = CurrentName
        )
    ) + 0

Worked example file: https://pwrbi.com/so_55273515/

Upvotes: 1

OscarLar
OscarLar

Reputation: 1335

In your case I would create a third table, a lookup table. With distinct values of Name from both table 1 and table 2. So that the Lookup table only has one 'A', one 'B' and so on.

Then create a 1:* relationship between the lookup table and each of the two tables.

Then you can create calculated columns in the lookup table where you check if and how many times the current row name in the lookup table exists in each of the two other tables.

I've done the exact thing when comparing two databases which were supposed to be identical but I could show that there were slight differences through this method.

Let me know if this helps or not.

Cheers,

Oscar

Upvotes: 0

Related Questions