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.
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
Please try this expression in a table visual with the Country and Name columns.
VAR thismin = [number]
VAR allmin =
MINX ( ALL ( Table1[Country], Table2[Name] ), [number] )
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.
@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 )
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?
@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])
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!