Is there a way to execute an if statement row by row?

[makcim392]

Hi everyone,

I'm trying to get the top 20 percent of some clients. I'd like to create a filter which looks at the client data, if they are on the top 20 percent, write the value, if not, leave it blank. So that then I can filter in a table, summing the rows that have data (leaving the blank values outside the calculation). All these values summed (displayed in a row), are compared against some dates (which are in columns, the dates I mean)

Problem is that I cannot execute an if statement row by row. I'm comparing 2 values from the same row as an assertion, and leaving it blank if its lower than the other value, otherwise copy the value from one of the values I'm comparing in the statement. 

I'm also having issues with the last part, for some reason power bi isn't suggesting the column I want to use for copying the value. 

Thanks!

[VasTg · Accepted solution]

@makcim392 

Yes.. Two options.

1. Create a conditional column or custom column in Query editor(Transform data )

or 

2. Create a calculated column and write DAX

If it helps, mark it as a solution

Kudos are nice too

[v-yuta-msft · Accepted solution]

@makcim392 ,

If you want to achieve this in power query, you can create a custom column in query editor, you can refer to doc:

https://docs.microsoft.com/en-us/power-bi/desktop-add-custom-column

In you want to add a new column in table using dax, please create a calculate column using dax function IF():

https://docs.microsoft.com/en-us/dax/if-function-dax

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @makcim392 ,

check this out.

SUMX

Returns the sum of an expression evaluated for each row in a table.

https://docs.microsoft.com/en-us/dax/sumx-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[amitchandak]

Refer

https://www.burningsuit.co.uk/blog/2019/05/find-top-or-bottom-n-percent-dax/

https://community.powerbi.com/t5/Desktop/Top-N-percent-filter/td-p/682552

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[VasTg · Accepted solution]

@makcim392 

Yes.. Two options.

1. Create a conditional column or custom column in Query editor(Transform data )

or 

2. Create a calculated column and write DAX

If it helps, mark it as a solution

Kudos are nice too