Forum Discussion

Vijay08V's avatar
Vijay08V
Helper III
2 years ago

Lookup Rating based on Filter Condition

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 :

CategoryWeightage %RankingExpected Output
AB108.66%2Doesnot
AB117.29%1Meet
AC109.18%2Doesnot
AC111.44%1Meet

 

Table B Sample :

 

CategoryRatingRank
ABDoesnot2
ABExceed1
ABFar Exceed1
ABMeet1
ACDoesnot2
ACExceed1
ACFar Exceed1
ACMeet1
Need Help

8 Replies

  • Vijay08V's avatar
    Vijay08V
    Helper III
    2 years ago
    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's avatar
      Vijay08V
      Helper III
      2 years ago
      CategoryWeightage %RankingExpected Output
      AB108.66%2Doesnot
      AB117.29%1Meet
      AC109.18%2Doesnot
      AC111.44%1Meet
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.