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
rmcneish
Helper I
Helper I

How to create colum with max values with conditions

Hi dear friends.

I need add a column with a max value for sales taking as conditions three fields. Basically, i need the maximum value for sales by turno, planta and fecha.

PBI.JPG

Thank you for your help.

Regards.

@Vvelarde @Ashish_Mathur 

4 REPLIES 4
AlB
Super User
Super User

Hi @rmcneish,

Edited

Try this for a calculated column in the table you show (Table1):

NewColMaxSales =
CALCULATE ( MAX ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[PLANTA], Table1[FECHA P.], Table1[TURNO] ) )

 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

 

@AlB 

I try that, but not found yet, throws me the value of the whole table.

I upload my pbix, and the column that i am creating is "MAX HOUR" from table ZQM_CNTL_P_DIARI.

MAX HOUR = CALCULATE(MAX(ZQM_CNTL_P_DIARI[HORA]),ALLEXCEPT(ZQM_CNTL_P_DIARI,ZQM_CNTL_P_DIARI[PLANTA],ZQM_CNTL_P_DIARI[TURNO],ZQM_CNTL_P_DIARI[FECHA P.]))

http://bit.do/eNpJ4

 

The measure appears to be working fine.  I see it return the correct values. 

The issue you might have is that [HORA] is taking values above 24 hours and that makes it roll over to the next day. Look for instance for the value 31/12/1899 07:00:00 in [HORA] if you see it as DateTime. Values under 24 hours should remain in 30/12/1899, which is the 0 in DAX DateTime type.  So you might have to check that out. 

I would also turn of the option Auto DateTime. It can cause unexpected behavior. Check this posting for more info 

 

Hi @AlB .

This table only is a litle sample from my big table with 47 fields.

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.