topic Re: DAX Measure: group by min & if condition in DAX Commands and Tips

DAX Measure: group by min & if condition

qsong — Thu, 20 Aug 2020 22:43:50 GMT

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

Re: DAX Measure: group by min & if condition

Greg_Deckler — Thu, 20 Aug 2020 22:53:24 GMT

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.

Re: DAX Measure: group by min & if condition

qsong — Thu, 20 Aug 2020 23:21:12 GMT

Greg_Deckler , thank you for your quick reply.

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

Thank you

Re: DAX Measure: group by min & if condition

Greg_Deckler — Fri, 21 Aug 2020 01:31:57 GMT

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

Re: DAX Measure: group by min & if condition

amitchandak — Fri, 21 Aug 2020 03:29:03 GMT

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 )

Re: DAX Measure: group by min & if condition

qsong — Fri, 21 Aug 2020 13:22:30 GMT

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

Re: DAX Measure: group by min & if condition

Anonymous — Fri, 21 Aug 2020 13:28:01 GMT

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)

Re: DAX Measure: group by min & if condition

qsong — Fri, 21 Aug 2020 13:31:00 GMT

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.

Re: DAX Measure: group by min & if condition

mahoneypat — Sat, 22 Aug 2020 21:58:29 GMT

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

Re: DAX Measure: group by min & if condition

Anonymous — Sun, 23 Aug 2020 02:07:16 GMT

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.