Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Employee
Employee

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

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

 

@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
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors