Return the smallest value from a column based on the minx function

[Anonymous]

Hey Guys ! Hope Everything is good for you all !

I'm having some difficulties making a mesure to return values based on an interactive function ( Minx in this case ).
Basically , I'm Calculating MTBF. ( Mean Time Between Failures ) in relation with all machines in my Dimension table.

MTBF = DIVIDE([Tempo Disponível],[Quantidade de Paradas])

Menor MTBF = MINX(VALUES(F_MTBF[machine]),'Mesures MTBF'[MTBF])

And this works fine.

The problem is, when I'm trying to return the machine of the smallest Value. Because it is an interactive function and is defined as a measure, I cannot use the "Min" function to do this calculation.

Using filters , I'm able to reach my goal as show below:

N Superior Filter and THe function Menor 

I believe it's a TOPN function but when I do this type of calculation using Minx as an expression I get an error because it uses multiple columns and not a scalar (Due to the fact that MINX is an interactive function).

Its My dimension Table

So, How can i create a dax measure using the same way of this filter ?

[Anonymous · Accepted solution]

Finally found a solution :

Thank you guys !

Menor Máquina = 
VAR Tabela =
    SUMMARIZE (
        F_MTBF,
        F_MTBF[Equipamento],
        "Total Vendas", [Menor MTBF]
    )
    
VAR MenorMTBF = 
MINX (
    SUMMARIZE (
        F_MTBF,
        F_MTBF[Equipamento],
        "Maior Valor", [Menor MTBF]
    ),
    [Maior Valor]
)
RETURN
    CALCULATE (
        FIRSTNONBLANK (
            F_MTBF[Equipamento],
            1
        ),
        FILTER (
            Tabela,
            [Total Vendas] = MenorMTBF
        )
    )

[Anonymous]

Hello @goncalogeraldes  ! , Thanks for your Answer !

I tried and returned an Empty Value

I created a RankX function to figure it out if there any problem and my rank starts from value 2 ( Skipping 1 in ASC ordering )

Rank Machine = RANKX(ALL(D_Machine[machine]),'Medidas MTBF'[MTBF],,ASC,Dense)

But, modifing your measure to show 2 or 5 itens continues empty in return.

Regards !

[goncalogeraldes]

Try this:

Rank Machine =
RANKX (
    ALLNOBLANKROW ( D_Machine[machine] ),
    'Medidas MTBF'[MTBF],
    ,
    ASC,
    DENSE
)

[Anonymous]

Worked ! Thank You !

But, after a few tests i realized that your formula to the smallest machine is not working.

Returns a blank value 

And if I limit the machines, the result of the lowest value doesn't match

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[goncalogeraldes]

Hello there @Anonymous ! Check if this works for you:

Menor MTBF =
CALCULATE (
    SELECTEDVALUE ( F_MTBF[machine] ),
    TOPN (
        1,
        SUMMARIZE (
            F_MTBF,
            F_MTBF[machine],
            "mtbf", DIVIDE( [Tempo Disponível] , [Quantidade de Paradas] )
        ),
        [mtbf], DESC
    )
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes