fetch Country dynamically in textbox where "Sc-measure" is least for selected(mon-year) in slicer

[Sania-F]

Hi  team,

requiremnet : In a text box, I dynamically need to show country name, where the "SC" value is minimum for selected period[month-year] in slicer.

Data available :
I have dim country table with country column.
"SC_measure" is a measure that gets calculated at run time only. country table and the "SC" measure do not have any direct/indirect relationship.
Period is  a calculated table and [Month-Year] is a calculated column fetching data as "Jan-fy24", "Feb-FY24" and so on... 

I  have written 2 DAX formulae.

1. To fetch min sc value for selected period

Min_SC = 

          VAR SelectedPeriod = SELECTEDVALUE(period[Month-year])

RETURN

CALCULATE(

    MINX('Measure Table',[SC_measure]), period[Month-year] = SelectedPeriod)
    /*MINX('Measure',[SC_measure]) --taking mininum sc  value*/

===============

2. DAX to fetch Country name where SC is minimum based on selected period[month-year] in slicer, and the country should not be blank or NA

CountryNameWithMinSC =

VAR SelectedPeriod = SELECTEDVALUE(period[Month-year])

/*VAR MinValue = [Min_SC]*/

VAR MinValue = CALCULATE([Min_SC], period[Month-year] = SelectedPeriod)

RETURN

MAXX(

    FILTER(

        DimCountry,

        NOT ISBLANK(DimCountry[country]) && -- Filter out blank country

        CALCULATE(

            [Min_SC],

            ALLEXCEPT(DimCountry, DimCountry[country]),

            period[Month-year] = SelectedPeriod  // Apply filter for selected period

        ) = MinValue

    ),

    DimCountry[country]

) /*retrieve country name where the sc is least*/

But my above DAX measures are not working whenever I am selecting month-year dynamically in slicer. Please help.

Thankyou for your help.

[rajendraongole1]

Hi @Sania-F -Try the below create measure to calculates the minimum SC value

Min_SC =
VAR SelectedPeriod = SELECTEDVALUE(period[Month-year])
RETURN
CALCULATE(
MINX('Measure Table', [SC_measure]),
period[Month-year] = SelectedPeriod
)

Create another measure to calculate the countries correctly with minimum one SC value.

CountryNameWithMinSC =
VAR SelectedPeriod = SELECTEDVALUE(period[Month-year])
VAR MinValue = [Min_SC]
RETURN
MAXX(
FILTER(
ADDCOLUMNS(
DimCountry,
"SCValue", CALCULATE(
[SC_measure],
period[Month-year] = SelectedPeriod
)
),
NOT ISBLANK([SCValue]) &&
[SCValue] = MinValue
),
DimCountry[country]
)

I hope the above approach works. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Sania-F]

hi @rajendraongole1 

I tried the above DAX but its not working. 

I strongly feel that something needs to be fixed with DAX 1 measure , because the 1st dax is not returning any value, when I try to see in a card/table what value is it returning 

Min_SC =
VAR SelectedPeriod = SELECTEDVALUE(period[Month-year])
RETURN
CALCULATE(
MINX('Measure Table', [SC_measure]),
period[Month-year] = SelectedPeriod
)

I also tried to eliminate/ignore if there are any blank values while fetchinh min sc value, but still not working. it just returns blank. any idea what may be going wrong.

[rajendraongole1]

Hi @Sania-F - Can you try below measure for DAX1 AND DAX2:

Changed logic using treatas If your tables do not have a direct relationship, use the function to apply the filter context

Min_SC =
VAR SelectedPeriod = SELECTEDVALUE(period[Month-Year])
RETURN
CALCULATE(
MINX('Measure Table', [SC_measure]),
TREATAS( { SelectedPeriod }, 'Measure Table'[Month-Year] )
)

Dax2: modified one.

CountryNameWithMinSC =
VAR SelectedPeriod = SELECTEDVALUE(period[Month-Year])
VAR MinValue = [Min_SC]
RETURN
MAXX(
FILTER(
CROSSJOIN(DimCountry, 'Measure Table'),
NOT ISBLANK(DimCountry[country]) &&
DimCountry[country] <> "NA" &&
'Measure Table'[Month-Year] = SelectedPeriod &&
[SC_measure] = MinValue
),
DimCountry[country]
)

Try above and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Sania-F · Accepted solution]

HI @rajendraongole1 

The below single DAX worked to fetch the country for min sc_measure.

MinimumSCMeasureAcrossCountries =
VAR Countries =
SUMMARIZE(
DimCountry,
DimCountry[country]
)
VAR MinSCMeasure =
MINX(
Countries,
[SC_Measure] 
)
VAR MinSCMeasureUnit =
SELECTCOLUMNS(
FILTER(
Countries,
[SC_Measure] = MinSCMeasure
),
"country", DimCountry[country]
)
RETURN
MinSCMeasureUnit