DAX Measure: group by min & if condition

Question

I have several dataset tables in PowerBI report. The column country comes from TABLE1 while the column name comes from TABLE2.

So firstly I want to calculate min_number based on country and name, and then if min_number = number, the min will be 1; otherwise, 0. So the result table looks like:

This is my code for min

min = 
VAR min_number =
    CALCULATE (
        MIN ( [number] ),
        ALLEXCEPT ( TABLE1, TABLE1[country] ), ALLEXCEPT (TABLE2, TABLE2[name])
    )
RETURN
    IF ( [number] = Min_number,1, 0 )

I got an error: the MIN function only accepts a column reference as the argument number 1. Does it mean if it has to be one condition? how to fix it? Thank you

Answer 1

I would solve it by just making two separate measures, since we want to see the both results in the final table anyway.

First the min_number calculation:

min_number = CALCULATE(MIN('Table'[number]);ALLEXCEPT('Table';'Table'[country];'Table'[name]))

And the min measure:

min = IF(MAX('Table'[number]) = [min_number];1;0)

As we are using a measure, we can use MAX, so it will know what number to reference in the IF. It will still use the MAX number per row, so results are correct.

Answer 2

You can try with this below measure-

min = 

VAR current_row_country = MIN(table1[country])
VAR current_row_name = MIN(table1[name])
VAR current_row_number = MIN(table1[number])

VAR min_number =
    CALCULATE (
        MIN (table1[number]),
        FILTER(
            ALL(table1),
            table1[country] = current_row_country
                && table1[name] = current_row_name
        )
    )

RETURN IF (min_number = current_row_number,1, 0 )