MINX with Temp Table

[ribisht17]

Hello Community, 

I want to show Scores from different location and then pick the one with the lowest score.

Actually I need to show this via Bar Chart hence needed a temp table to show the overall minimum bar (I know with tables we can directly get the min values)

Location_Temp =

UNION(

    VALUES(Dim_Employee[Location]),

    ROW("Location", "Overlall Minimum")

)

1st way (working)

Location_Score_Summary =

SUMMARIZE(

    Fact_Employee_Training,

    Dim_Employee[Location],

    "Total Score", SUM(Fact_Employee_Training[Score])

)

 

 

test_minx = if( CONTAINSSTRING( MIN(Location_Temp[Location]),"Over"),

 

MIN(Location_Score_Summary[Total Score]),

CALCULATE(SUM(Fact_Employee_Training[Score])))

 

 

-----------------------------------------------------------------------------------------------------------------------------------------

2nd way (not working)

 

 

 test_minx_fixed =

IF(

    CONTAINSSTRING(SELECTEDVALUE(Location_Temp[Location]), "Over"),

    MINX(

        VALUES(Dim_Employee[Location]),

        CALCULATE(SUM(Fact_Employee_Training[Score]))

    ),

    CALCULATE(

        SUM(Fact_Employee_Training[Score]),

        Dim_Employee[Location] = SELECTEDVALUE(Location_Temp[Location])

    )

)

 

 Why second way gives me blank there?

Why it is not working for the second way ?

Regards,

Ritesh

[Shahid12523]

Your second formula returns blank because MINX lacks row context—CALCULATE(SUM(...)) isn’t filtered per location. Also, CALCULATE(..., Dim_Employee[Location] = ...) is invalid syntax unless wrapped in a FILTER().

Fix: Use FILTER() inside MINX to apply location context:

MINX(
VALUES(Dim_Employee[Location]),
CALCULATE(
SUM(Fact_Employee_Training[Score]),
FILTER(
ALL(Dim_Employee),
Dim_Employee[Location] = EARLIER(Dim_Employee[Location])
)
)
)

[ribisht17]

Thanks Shahid,

Unfortunately your solution is not working 

(update)
However, the same DAX works here

Regards,

Ritesh

[Jihwan_Kim · Accepted solution]

Hi,

Please try something like below.

test_minx_fixed = 
IF(
    CONTAINSSTRING(SELECTEDVALUE(Location_Temp[Location]), "Over"),
    MINX(
        FILTER(ALL(Location_Temp[Location]), Location_Temp[Location] <> "Overall Minimum"),
        CALCULATE(SUM(Fact_Employee_Training[Score]))
    ),
    CALCULATE(
        SUM(Fact_Employee_Training[Score]),
        Dim_Employee[Location] = SELECTEDVALUE(Location_Temp[Location])
    )
)

[ribisht17]

Hi @Jihwan_Kim 

Great this is working,accepting this as Solution , but I am still not sure why the below is not working ?

   MINX(

        VALUES(Dim_Employee[Location]),

        CALCULATE(SUM(Fact_Employee_Training[Score]))

    )

Same DAX is working in the below scenario

Regards,

Ritesh