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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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