Min value in a column by month without duplicates values (DAX or Power Query)

[Hernán]

Hello team,

I've tried to create a calculate column or a measure but I couldn't achieve my goal. I read in the forum similar situation but It didn't work for me.

I need to build a calculate column or a measure where: Every month selects the minimum value by store and by service. Thus:

Image1. Current matrix

Image2. I expected a column or measure with the min value in the store as the right table in the below image.

I expected:

In that case I filtered the year 2022 and month September. It is the reason it shows only info in sep2022. But, It applies for every month. The data is showed only by month and year (sep-2022, nov-2022...)

Note: The overall has three values: 1, 2, 3. Thus, the minimum values will be 1 and the maximum 3. If the Store has as overall zero, (null). The value zero, it won't count in the overall because it is a empty value. It musn't be taken in the "Min overall".

Attached .pbix, data.xlsx and I expected e.g. sep2022

Thank you so much for your help!

Regards,

Hernán Montoya

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

OA = SUM(Sheet1[Overall])

Measure = if(HASONEFILTER(Sheet1[City]),[OA],MIN(Sheet1[Overall]))

Hope this helps.