Return a value from a lookup table based off of a measure in a fact table

[Anonymous]

I have two related tables (1) a project listing[Project ID], [NAICS Code] table, and (*) a revenue table[Project ID], [Revenue].  I've calculated a measure for Revenue and I would like to return the [NAICS Code] in the Project ID table based on the MAX Revenue measure. All filters need to be removed to highlight the return value of the MAX Revenue by NAICS in a card visual. 

[bcdobbs · Accepted solution]

Try the following.

First a basic measure for total revenue:

Total Revenue = 
    SUM ( Revenue[Revenue] )

Then the following will return the project with max revenue (or if there are ties will return a comma separate list):

Top Project = 

VAR MaxRevenue = 
    CALCULATE(
        MAXX(
            VALUES(Project[ProjectId]),
            [Total Revenue]
        ),
        REMOVEFILTERS(Project)
    )

VAR Result =
    CONCATENATEX(
        FILTER(Project, [Total Revenue] = MaxRevenue),
        Project[NAICS Code],
        ", "
    )

RETURN Result