How to create a measure to return row entry using min ifs in DAX in Power BI?

[cliveb2016]

Hi, I am stuck on the following in power BI.

Suppose I have a table as pictured here:  

Now I want to write some DAX code to create a measure in PowerBI that will return some other information in another column (marked *) for each shop type, the MINIMUM value of the days open WHERE the owner type is Family OR Business.

So for instance for electronics shops there is only 2 shops that are either family or business (in fact they are both business), the minimum of the days opened for these shops is 30 days. This corresponds to Row 4. Therefore I would like the measure to return some other entry in row 4, (it could be the shop owner or the yearly revenue or some other information contained in row 4).

For simplicities sake let's suppose I know that a minimum days since opening value exists amongst each shop type and is unique.

I have tried quite a few things but can't seem to get it to work!

Any help would be appreciated.

Thank you.

[v-yuta-msft · Accepted solution]

Hi cliveb2016,

To achieve your requirement, create a calculate column using DAX as below:

* = 
CALCULATE (
    MIN ( Table1[Days Since Opening] ),
    FILTER (
        Table1,
        Table1[Shop Type] = EARLIER ( Table1[Shop Type] )
            && ( Table1[Owner Type] = "Family"
            || Table1[Owner Type] = "Business" )
    )
)

 

Regards,

Jimmy Tao

[cliveb2016]

Thank you. I must admit it took me a while to try and get my head around EARLIER, but I'm almost there.

One question though, is there a way to bring back a value that is not in the row but is instead in the row with the minimum value.

Hopefully the following example will make it clearer.

Take rows 1 and 2 of my table. They are both family owned sports stores that have been open for 50 and 60 days respectively.

Let's suppose I added a column called Shop Owner and suppose store 1 is owned by Bill and store 2 is owned by Fred.

Is there a way to instead of returning the minimum value of the days since opening column (as the formula you made currently does) and instead return the owner of the shop associated with that minimum number of days since opening.

So in my example the calcualted column would go from 50 50, to Bill Bill (since Bill owns the 50 day since open store?)

Thanks!

[cliveb2016]

Any help on this?