Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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?
Thank you
HI @qsong ,
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@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.
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |