DAX Measure: group by min & if condition

[qsong]

Hi PowerBI users,

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

country	name	number
A	ICE	34
A	ICE	25
A	WATER	18
B	ICE	26
B	ICE	89
B	WATER	32
B	WATER	31

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:

country	name	number	min_number	min
A	ICE	34	25	0
A	ICE	25	25	1
A	WATER	18	18	1
B	ICE	26	26	1
B	ICE	89	26	0
B	WATER	32	31	0
B	WATER	31	31	1

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

[Anonymous]

The table you've shown as first in your question is not possible in Power BI. As far as I know, there is no way for a visual to generate 2 identical rows with different values of a measure. It's not logically possible because if that was the case, you'd have a measure that for the same selection returns different values.

[mahoneypat]

Please try this expression in a table visual with the Country and Name columns.

Min =
VAR thismin = [number]
VAR allmin =
MINX ( ALL ( Table1[Country], Table2[Name] ), [number] )
RETURN
IF ( thismin = allmin, 1, 0 )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@qsong , Can you provide data how it coming from both tables.  In Number is already min, no need for MIN. Else try MINX. refer

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

[qsong]

@amitchandak 

So country comes from the TABLE1, while name is from TABLE2. Both tables are related in a common key. 

The number is not minimal by country and name, you can see the number is different from  min_number which is the result.

The formula you suggested me does not work, because syntax is not correct. 

[qsong]

@Greg_Deckler Yes, the both tables are related, and share the same key. 

I employed the formula you suggested me for one group:

MinScoreMeasure = MINX ( SUMMARIZE ( Table1, Table1[Country] , "Measure",[number] ), [Measure])

I am not sure the meaning of Measure, for this case, should it be MIN?

Thank you

[harshnathani]

HI @qsong ,

https://dax.guide/minx/

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Greg_Deckler]

@qsong This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[qsong]

@Greg_Deckler , thank you for your quick reply.

what happen if the groups come from different tables: TABLE1 and TABLE2? 

Thank you

[Greg_Deckler]

@qsong - Well I would expect that the two tables would have to be related, correct?