Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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