Need help with a DAX calculation of my Complex Scenario

[Anonymous]

I am trying to calculate Star rating provided to my applications to be rolled up to Workload level.

Any help would be greatly appreciated.

Data  :

Rules for the required calculation : 

1 ) When the total servers is 0 then there is no star Rating
2) Rating is given at application level - Workloads with onlyl 1 application will have the star rating of the workload (For example Wk1 and Wk2)
3) Rules for Grouping at workload level (For workloads with more than 1 app)
(Gold or Silver or bronze) + Bronze = Bronze (Example Wk3)
(Gold or Silver or bronze) + None = None (WK7)
(Gold or Silver or bronze) + NA = (Gold or Silver or bronze - Whever is lower) (Example Wk6)
All NA = NA

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume [Star rating] is a column,

create measures

counts = CALCULATE(DISTINCTCOUNT(Table1[app]),ALLEXCEPT(Table1,Table1[workload]))

text_content = CONCATENATEX(FILTER(ALL(Table1),Table1[workload]=MAX(Table1[workload])&&[counts]>1),[star rating])

None_find = FIND("None",[text_content],1,0)

NA_find = FIND("NA",[text_content],1,0)

condition =
IF (
    [NA_find] > 0,
    IF (
        FIND ( "Sliver", [text_content], 1, 0 ) > 0,
        "Slicer",
        IF (
            FIND ( "Bronze", [text_content], 1, 0 ) > 0,
            "Bronze",
            IF ( FIND ( "Gold", [text_content], 1, 0 ) > 0, "Gold" )
        )
    )
)

final = IF([counts]=1,MAX([star rating]),IF([None_find]>0,"None",IF([NA_find]>0,[condition])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.