Comparing two tables and returning mismatched names

[Anonymous]

Hi all,

I have two tables, one is the parent table containing all company names, other is a subset with a fewer company names.

I want to create a measure to return "rated" when table 2 contains company from table 1 and "not rated" when company name does not exist in table 1.

Thanks for your help in advance.

NOTE: The number of columns in both the tables are different

[TheoC · Accepted solution]

@KrtinM here is a measure you can use:

Measure = 

VAR _1 = MAX ('Table'[Company Name] )
VAR _2 = LOOKUPVALUE ( 'Table (2)'[Company Name] ,'Table (2)'[Company Name] , _1 )

RETURN

IF ( _2 = BLANK() , "Not Rated" , "Rated" )

Output per below:

Thanks,

Theo

[danextian]

Hi @Anonymous ,

You can have many solutions to this depending on how your data model is. If the subset contains unique company names, you can create a one to many relationship from that table to subset to the parent table using the company columns. In the parent table which is on the  many side  of the relationship ( assuming a company name appears more than one), you can create a calculated column similar to below:

Rated?(Column) =
IF ( RELATED ( Subset[Company] ) <> BLANK (), "Rated", "Not Rated" )

If you really want this as a measure, use this but that would be a double work.:  

Rated? (Measure) = 
SELECTEDVALUE (Parent[Rated?(Column)])

Alternatively, you can create  calulcated column in Parent using LOOKUP function.

[Anonymous]

Hi Thanks for your reply,

I already have a calculated column in parent with rated and non rated, problem is, when i select values from slicers in table b, it wont filter visuals made from table 1 because of the relationship.

[TheoC]

@KrtinM here is a measure you can use:

Measure = 

VAR _1 = MAX ('Table'[Company Name] )
VAR _2 = LOOKUPVALUE ( 'Table (2)'[Company Name] ,'Table (2)'[Company Name] , _1 )

RETURN

IF ( _2 = BLANK() , "Not Rated" , "Rated" )

Output per below:

Works with and without relationships.

Thanks,
Theo

[TheoC]

Hi @Anonymous 

I created 2 tables. Company D is missing from Table 2.

Table 1: 

Table 2:

Ensure there's a relationship between the two:

I then created a Calculated Column in Table 1 using RELATED and also using IF to determine if RELATED returns a Company. If it does, return "Rated" otherwise return "Not Rated".

_Rated = 

VAR _1 = RELATED ('Table (2)'[Company Name] )

RETURN

IF ( _1 = 'Table'[Company Name] , "Rated" , "Not Rated" )

Output is as follows:

Hope this helps. PBIX file attached for further assistance.

Theo 🙂

[Anonymous]

Thanks for your reply Theo, I already have a calculated column like that, issue being table 1 filters table 2, if I go with filter both sides, i get other issues, hence I want a measure so that relationship issues won't appear. Am i making sense, apologies new to Power BI.

[TheoC]

@Anonymous here is a measure you can use:

Measure = 

VAR _1 = MAX ('Table'[Company Name] )
VAR _2 = LOOKUPVALUE ( 'Table (2)'[Company Name] ,'Table (2)'[Company Name] , _1 )

RETURN

IF ( _2 = BLANK() , "Not Rated" , "Rated" )

Output per below:

[danextian]

Hi @Anonymous ,

You can try this measure.

Rated/Not Rated =
VAR __count =
    CALCULATE (
        DISTINCTCOUNT ( 'Parent'[Company] ),
        FILTER ( 'Parent', 'Parent'[Company] IN VALUES ( 'Subset'[Company] ) )
    )
RETURN
    IF ( __count = BLANK (), "Not Rated", "Rated" )

Tested that it works with or without a relationshi between the two tables.

[TheoC]

@KrtinM here is a measure you can use:

Measure = 

VAR _1 = MAX ('Table'[Company Name] )
VAR _2 = LOOKUPVALUE ( 'Table (2)'[Company Name] ,'Table (2)'[Company Name] , _1 )

RETURN

IF ( _2 = BLANK() , "Not Rated" , "Rated" )

Output per below:

Thanks,

Theo