cancel
Showing results for
Did you mean:
Helper II

## DAX Measure: group by min & if condition

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

9 REPLIES 9
Solution Sage
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.
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Super User IV

@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 )```

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper II

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.

Helper II

@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

Super User III

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)

Super User IV

@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.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper II

@Greg_Deckler , thank you for your quick reply.

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

Thank you

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors