Tricky measure with dax

[Meca2u]

Hi

I have the following table, with a filter on date at the top.

I am trying to create a measure in Power BI Desktop called Denominator that will return something like this...

So basically i want to return the Value from the Minimum Period against Country, State and Colour and put it against all rows for each Country, State and Colour combination.

Any ideas?

[Meca2u · Accepted solution]

Thanks,

Below the dax i was looking for....

Denominator =

var _min_period=

MINX(

    FILTER(ALLSELECTED('Query1'),Query1[Country] = MAX(Query1[Country]) && Query1[State] = MAX(Query1[State]) && Query1[Colour] = MAX(Query1[Colour]) ),

    'Query1'[Period])

var _min_value=

MINX(

    FILTER(ALLSELECTED('Query1'),Query1[Country] = MAX(Query1[Country])  && Query1[State] = MAX(Query1[State]) && Query1[Colour] = MAX(Query1[Colour]) && Query1[Period]=_min_period),[Value])

RETURN _min_value

[Anonymous]

Hi @Meca2u  ,

Please follow these steps:

1.Create metrics that return the smallest value for the smallest date after sorting by country, state, and color.

Denominator = 
var _min_period=
MINX(
    FILTER(ALLSELECTED('Table'),'Table'[Country] = MAX('Table'[Country]) && 'Table'[State] = MAX('Table'[State]) && 'Table'[Colour] = MAX('Table'[Colour])),
    'Table'[Period])
var _min_value=
MINX(
    FILTER(ALLSELECTED('Table'),'Table'[Country] = MAX('Table'[Country]) && 'Table'[State] = MAX('Table'[State]) &&'Table'[Period]=_min_period),[Value])
return
IF(
    MAX('Table'[Period])=_min_period&&MAX('Table'[Value])=_min_value,_min_value,BLANK())

2.The results obtained are as follows.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pratyashasamal]

Hi @Meca2u ,

You can this calculation :-

Column = CALCULATE(MIN(Table1[Value]),FILTER(Table1,Table1[Country]=EARLIER(Table1[Country]) && Table1[State] =EARLIER(Table1[State]) && Table1[Color] =EARLIER(Table1[Color]))))

Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C