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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Polly
Helper I
Helper I

Feature Scaling using Dax

Hi,

 

I wonder if any one can help.

 

I pretty new to powerBI and Dax and I am struggling to normalsie a variable using the Feature Scaling Method. I am trying the below but it is bring up the following error message, even though all instances of the column are agregated

A single value for column 'Cancellation Rate Indexed' in table 'Agent Lookup' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.  

 



Feature Scaling for Cancellation Rate =

 

(SUM('Agent Lookup'[Cancellation Rate Indexed])-MIN('Agent Lookup'[Cancellation Rate Indexed],ALL('Agent Lookup')))

/

 

(MAX('Agent Lookup'[Cancellation Rate Indexed],ALL('Agent Lookup'))-MIN('Agent Lookup'[Cancellation Rate Indexed],ALL('Agent Lookup')))

 

 

Thanks

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Polly,

 

Based on your formula. I think you want to use current value to deal with max/min value from current table, right?

If this is a case, you should use calculate or minx/maxx function to apply the filter for this formula.

Result =
DIVIDE (
SUM ( 'Table'[Amount] ) - MINX ( ALL ( 'Table' ), 'Table'[Amount] ),
MAXX ( ALL ( 'Table' ), 'Table'[Amount] )
- MINX ( ALL ( 'Table' ), 'Table'[Amount] )
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @Polly,

 

Based on your formula. I think you want to use current value to deal with max/min value from current table, right?

If this is a case, you should use calculate or minx/maxx function to apply the filter for this formula.

Result =
DIVIDE (
SUM ( 'Table'[Amount] ) - MINX ( ALL ( 'Table' ), 'Table'[Amount] ),
MAXX ( ALL ( 'Table' ), 'Table'[Amount] )
- MINX ( ALL ( 'Table' ), 'Table'[Amount] )
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks!

Thanks!

TomMartens
Super User
Super User

Hey,

 

on 1st look you should define your measure like this

your measure = 
(
SUM('Agent Lookup'[Cancellation Rate Indexed])-
CALCULATE(
  MIN('Agent Lookup'[Cancellation Rate Indexed])
  ,ALL('Agent Lookup')
)
)

/
(
CALCULATE(
  MAX('Agent Lookup'[Cancellation Rate Indexed])
  ,ALL('Agent Lookup')
)
-
CALCULATE(
  MIN('Agent Lookup'[Cancellation Rate Indexed])
  ,ALL('Agent Lookup')
)
)

Basically you also can store the MIN and MAX values to a variable and then use these variables in the expression, this not just improve readibility but also improve performance

 

Hope this helps

 

Regards

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.