IS NUMBER AND SEARCH FUNCTIONS IN POWER BI

[HagenHawke]

Hi Community,

I am very new to Power BI. I just completed my Power BI Beginners and Intermediate courses 3 weeks ago and taking on a small project within my team to convert one report from excel to Power BI. I am the first person in my wider team to use Power BI and really in need of some guidance.

I want to add an aditional function to my growth calculations to say that "If the (Table[Metric] contains "%" then just give me the value in 2H19"  and not to apply the 'growth calculation' as highlighted in 'blue' in the table  below.

I want layer it to the growth formula that I wrote below:

Growth =

var currperiod = sum(MasterData[Value])

Var relativeperiod = CALCULATE(SUM(MasterData[Value]),USERELATIONSHIP(dim_RelativePeriod[Period],MasterData[Period]),ALL(dim_Period))

return currperiod relativeperiod -1

Below is an example of my data:

Company	Metric	1H18	2H19	Growth
A	Cash earnings	1200	2208	84.00%
A	Net Interest Income	5000	6225	24.50%
A	Total Operating Expenses	-2554	-2518	-1.41%
A	Net Interest Margin %	-2%	-3%	-3%
C	Cash earnings	121	269	122.31%
C	Net Interest Income	321	520	61.99%
C	Total Operating Expenses	-621	-369	-40.58%
C	Net Interest Margin %	-3%	4%	4%
E	Cash earnings	1251	2968	137.25%
E	Net Interest Income	7215	6213	-13.89%
E	Total Operating Expenses	-4215	-4578	8.61%
E	Net Interest Margin %	-5%	-6%	-6%

Thank you so much in advance for your help.

Kind regards,

M

[v-jingzhang · Accepted solution]

Hi @HagenHawke 

If your original table is like the following table (values in a column will be converted into the same format so the rate values with % will be converted into general decimal numbers),

You can create a new column with below DAX code. 

Growth = IF(CONTAINSSTRING('MasterData'[Metric],"%"), 'MasterData'[2H19], DIVIDE('MasterData'[2H19] - 'MasterData'[1H18], 'MasterData'[1H18]))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.