Excel IF statement to DAX

[wilreynolds]

Hello everyone, this forum has been insanely helpful for me, over the last 13 months, for the first time, I have to post to get my answer, so I am hoping you all can help.

I'm trying to get started in how to replicate this excel formula into DAX:

=IF(SUM(NOT(ISERROR(FIND(Categories!$A$1,$A7271)))*1)>0,K$1,"Non-"&LOWER(K$1))

Thank you everyone!

[v-yuta-msft]

Hi wilreynolds,

There is no row number in power bi table so you should create an index column to represent row number such as $1 $7271 in excel. Power bi has the same function such as if(), sum(), not(), iserror(), find() and lower() but the usage is a little bit difference with functions in excel. Could you give a sample data and your expected result so i can try to reproduce that in power bi?

Regards,

Jimmy Tao

[wilreynolds]

Hi Jimmy,

Thanks for your help. 

I added the index column but still ran into issues with referencing the data I wanted the cell to match. 

I would be expecting the cell to examine a string of text in a defined cell to contain the text from another defined cell. This formula in excel works very similarly to the Advanced Filter option in Power BI, but allows for the selection of both a "Contains" and "Does not contain" option based on the text found elsewhere. Here is a screen shot of how it looks in action in excel:

We utilize this formula in each column where you see red text as a result to layer multiple filtering options across multiple trigger text. For each trigger (storage, unit, container, etc.) the result can only be either that the Keyword in Column A either contains or does not contain the trigger. Layering multiple filters across multiple columns results in an AND statement rather than an OR statement, meaning the resulting list must Contain what I have designated AND must not contain what I have designated.

Ideally, the triggers at the top of each column in this excel version would be contained in a separate table in Power BI.

Very interested to hear your thoughts and appreciate all of your help.