cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMDNA Senior Member
Senior Member

Custom column - "best of these choices"

Hi all,

 

I have a table (see below) with the Absolute Percent Error of three different models. I'd like to create a custom column with the "best" (lowest APE) of those three.

 

In Excel, I have =MIN(Table[@[Model 1 APE]:[Model 3 APE]])

 

What's the PQ equivalent? I'm unsure how to specify a range of columns.

 

1.PNG

 

As usual, thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
SteveCampbell Established Member
Established Member

Re: Custom column - "best of these choices"

So really, we should not use a calculated column in DAX. It will result in the column being recaulclated every time and slow performance.

You could write a measure, which would not store a value for every row. As you asked for a column, instead, we should do this in the query editor. This will mean it is only calculated on a data refresh, and stored in the model.

 

In the query editor, go to the query, and select "Add Custom Column".

 

The code you can use is:

 

 

each if [Model 1 APE] = [Model 2 APE]then "N/A"

else if [Model 1 APE]< [Model 2 APE] then "Model 1 APE"
else if [Model 2 APE]< [Model 1 APE] then "Model 2 APE"
else "N/A"

Here, the second else if statement is a bit redundant, but I left it in so that you can add more logic if you wanted, to compare more models. You can add as many "else if" statements to use any logic you wish.

 

View solution in original post

4 REPLIES 4
SteveCampbell Established Member
Established Member

Re: Custom column - "best of these choices"

 

You can try:

New Column = 
_min_amnt = min([Model 1 APE],min([Model 2 APE],[Model 3 APE])

RETURN 

SWITCH( _min_amnt, 
  [Model 1 APE], "Model 1 APE",
  [Model 2 APE], "Model 2 APE",
  [Model 3 APE], "Model 3 APE",
  "None"
)

 

Although, this will not work in case of ties, and pick the first. What would be the desired solution if all 0, for example?

 

RMDNA Senior Member
Senior Member

Re: Custom column - "best of these choices"

@SteveCampbell,

 

How about with two columns (Model 1 and 2)? In the case of a tie/all zero, display "N/A."

SteveCampbell Established Member
Established Member

Re: Custom column - "best of these choices"

So really, we should not use a calculated column in DAX. It will result in the column being recaulclated every time and slow performance.

You could write a measure, which would not store a value for every row. As you asked for a column, instead, we should do this in the query editor. This will mean it is only calculated on a data refresh, and stored in the model.

 

In the query editor, go to the query, and select "Add Custom Column".

 

The code you can use is:

 

 

each if [Model 1 APE] = [Model 2 APE]then "N/A"

else if [Model 1 APE]< [Model 2 APE] then "Model 1 APE"
else if [Model 2 APE]< [Model 1 APE] then "Model 2 APE"
else "N/A"

Here, the second else if statement is a bit redundant, but I left it in so that you can add more logic if you wanted, to compare more models. You can add as many "else if" statements to use any logic you wish.

 

View solution in original post

Highlighted
RMDNA Senior Member
Senior Member

Re: Custom column - "best of these choices"

Wow. Somehow, after almost three years of using Power BI, I entirely forgot about the Conditional Column button. Thanks for the support!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 293 members 2,842 guests
Please welcome our newest community members: