Forum Discussion

ribisht17's avatar
ribisht17
Super User
9 months ago
Solved

MINX with Temp Table

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

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    9 months ago

    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])
    )
)

     

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    9 months ago

    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's avatar
      ribisht17
      Super User
      9 months ago

      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

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    9 months ago

    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's avatar
      ribisht17
      Super User
      9 months ago

      Thanks Shahid,

       

      Unfortunately your solution is not working 

      (update)
      However, the same DAX works here

       

       

      Regards,

      Ritesh