Lookup Rating based on Filter Condition

[Vijay08V]

Hi,

I am trying to replicate an excel formula in Power Bi. I have two tables A and B. In table A I have weightage values and I have ranked it within each category. In Table B I have the rankings and its corresponding ratings for each category. I am trying a DAX logic that checks if Category of table A matches with table B and then lookup the rating for the rankings. Anyhelp would be appreciated. Thanks!

Excel calculation :
=IF($AB7=0,"Doesnot",LOOKUP(AC7,$AI$2:$AI$5,$AF$2:$AF$5))

Table A sample :

Category	Weightage %	Ranking	Expected Output
AB	108.66%	2	Doesnot
AB	117.29%	1	Meet
AC	109.18%	2	Doesnot
AC	111.44%	1	Meet

Table B Sample :

Category	Rating	Rank
AB	Doesnot	2
AB	Exceed	1
AB	Far Exceed	1
AB	Meet	1
AC	Doesnot	2
AC	Exceed	1
AC	Far Exceed	1
AC	Meet	1

[Vijay08V]

Apologies made correction to expected output of Table A Category Weightage % Ranking Expected Output AB 108.66% 2 Doesnot AB 117.29% 1 Meet AC 109.18% 2 Doesnot AC 111.44% 1 Meet

[Vijay08V]

Category	Weightage %	Ranking	Expected Output
AB	108.66%	2	Doesnot
AB	117.29%	1	Meet
AC	109.18%	2	Doesnot
AC	111.44%	1	Meet

[Anonymous]

Hi,@Vijay08V 

Regarding the issue you raised, my solution is as follows:

1.Create calculated column references:

way1 = 
IF (
    ISERROR (
        LOOKUPVALUE (
            'Table B'[Rating],
            'Table B'[Category], 'Table A'[Category],
            'Table B'[Rank], 'Table A'[Ranking]
        )
    ),
    "Meet",
    LOOKUPVALUE (
        'Table B'[Rating],
        'Table B'[Category], 'Table A'[Category],
        'Table B'[Rank], 'Table A'[Ranking]
    )
)

way2 = 
VAR ccate = 'Table A'[Category]
VAR crank = 'Table A'[Ranking]
RETURN
    MAXX (
        FILTER ( 'Table B', 'Table B'[Category] = ccate && 'Table B'[Rank] = crank ),
        'Table B'[Rating]
    )

2. Below are the measure I've created for your needs:

MEASURE = 
MAXX (
    FILTER (
        'Table B',
        'Table B'[Category] = MAX ( 'Table A'[Category] )
            && 'Table B'[Rank] = MAX ( 'Table A'[Ranking] )
    ),
    'Table B'[Rating]
)

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Vijay08V]

Hi @Anonymous  - I added somemore data and tried the DAX logic  which you had shared in a calculated column but in some records I was getting blanks. 

I am not able to attach the PBIX file here so I have added it in a google drive. Basically I am trying to lookup Ratings from Table  B based on the Lookupvalue column and Ranking column.

Share drive URL
https://drive.google.com/file/d/1PsMn-xrYEWwnAKsgrSJo_ZrRVilkeIUG/view?usp=sharing

Table A :

Table B :

[Anonymous]

Hi,@Vijay08V 

Thanks for the quick reply.

The reason why the previous result is null is because the same values as the category and rank columns in Table A are not queried in Table B, so the return is empty.

At the moment I have fixed it according to your needs, added a judgment condition, and if it returns empty, it returns "Blank"

I offer two options:
 

1.Create calculated column references:

Column = 
VAR ccate = 'Table A'[Category]
VAR crank = 'Table A'[Ranking]
var mm1=    MAXX (
        FILTER ( 'Table B', 'Table B'[Category] = ccate && 'Table B'[Rank] = crank ),
        'Table B'[Rating]
    )
RETURN IF(ISBLANK(mm1),"BLANK",mm1)

2. Below are the measure I've created for your needs:

Measure = 
VAR mm2=MAXX (
    FILTER (
        'Table B',
        'Table B'[Category] = MAX ( 'Table A'[Category] )
            && 'Table B'[Rank] = MAX ( 'Table A'[Ranking] )
    ),
    'Table B'[Rating]
)
RETURN IF(ISBLANK(mm2),"BLANK",mm2)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Vijay08V]

Hi @Anonymous  - Appreciate your response. Actually there shouldn't be any blanks in the ratings. I have added the sample excel data with sample calculation.  In the sample calculation tab I have tried to perform the lookup calculation for each subset of data. I am trying to replicate the same logic in power Bi but with the consolidated data based on Lookupvalue column field. I hope the attached excel data brings more clarity to my problem statement.

Sample data link :

https://docs.google.com/spreadsheets/d/1YL68pUOK4EVzHtxy1tjV_8mqRy-3lR_g/edit?usp=sharing&ouid=111591468610839431407&rtpof=true&sd=true

[Anonymous]

Hi,@Vijay08V 

Has the problem been solved? If it does, share your solution and accept it as a solution that will help other community members who have the same problem as you.

If not, can you share it in a different way? Due to our security policy, we do not have access to the data you provide at this time, so we recommend that you choose to use SharePoint or OneDrive.

Best Regards,

Leroy Lu

[Vijay08V]

Hi @Anonymous  - I have put sample data and sample calculation in Onedrive

Lookup.xlsx