cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsong
Helper II
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.

 

countrynamenumber
AICE34
AICE25
AWATER18
BICE26
BICE89
BWATER32
BWATER31

 

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:

 

countrynamenumbermin_numbermin
AICE34250
AICE25251
AWATER18181
BICE26261
BICE89260
BWATER32310
BWATER31311

 

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
daxer
Solution Sage
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.
mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User IV
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!

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

 

@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

 

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
Super User IV
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!




@Greg_Deckler , thank you for your quick reply.

 

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

Thank you

 

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




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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