cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

How to use IF statement and show the values taken from another source.

Hi All,

 

I am using the below code to get my output. It is basically checking the values from a list of values provided by the user and then giving it a score(1,0). I don't want the score to be hardcoded and can I use another table to get the score (which can be changed by the user and I just have to hit refresh to change the table structure). I just want to show the values taken from another source and not hard-code it , can it be done ?

 

ScoreGeo = IF(
SUMX(Keyword_Geo,
FIND(
UPPER(Keyword_Geo[Geo]),
UPPER(Combined_Data[WWS Geo])
,,0
)
) > 0,
1,
0
)

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: How to use IF statement and show the values taken from another source.

@abhishekpati86

 

Please try to create a calculated column with following formula in Barcelona table.

ScoreTrade_Update = 
CALCULATE (
    MAX ( Keyword[Score] ),
    FILTER (
        ALL ( Keyword ),
        FIND (
            UPPER ( Keyword[Keyword] ),
            UPPER ( Barcelona[End User Trade Name] ),
            ,
            0
        )
            > 0
    )
)

How to use IF statement and show the values taken from another source_1.jpg

 

Best Regards,

Herbert

6 REPLIES 6

Re: How to use IF statement and show the values taken from another source.

The below data shows the range of text value I am picking from and I just want to show the value beside it as a score ( for now I am harcoding it as 1 in the code).

 

Capture.JPG

 

Highlighted

Re: How to use IF statement and show the values taken from another source.

Below is a screenshot of how my data currently looks like and I just want the values to be picked from the 2nd column so that I don't have to hard code it in the code.

Data currently

---

APAC

EMEA

UNKNOWN

Data required format --

APAC 1

EMEA 1

UNKNOWN 1

So that the values can be manually updated by anyone and the changes will be reflected in the Power BI

Re: How to use IF statement and show the values taken from another source.

v-haibl-msft Super Contributor
Super Contributor

Re: How to use IF statement and show the values taken from another source.

@abhishekpati86

 

What is another table like? If it is like the table as below. You can use LOOKUPVALUE function to get the score.

 

How to use IF statement and show the values taken from another source_1.jpg

 

 

ScoreGeo = 
LOOKUPVALUE ( AnotherTable[Values], AnotherTable[Geo], Keyword_Geo[Geo] )

 

How to use IF statement and show the values taken from another source_2.jpg

 

Best Regards,

Herbert

Re: How to use IF statement and show the values taken from another source.

Hi @v-haibl-msft,

 

Below are how my two tables look like,

 

Barcelona Table - The column End User Trade Name is the one I am concerned about and have to find out the ones which contains the keyword.

 

Capture.JPG

 

Keyword Table - The table which contains the keywords to be matched and given a risk score.

 

Capture.JPG

 

I am trying to create a table that will give me the risk_score based on the score value on the keyword Table. I haven't been able to do that as I am harcoding it to be 1 as in the below case - 

 

ScoreTrade = IF(
SUMX(Trade_Name,
FIND(
UPPER(Keyword_Trade_Name[Trade_Name]),
UPPER(Barcelona_Data[End User Trade Name])
,,0
)
) > 0,
1,
0
)

 

The ScoreTrade shows a value of 1 as it matches the keyword from the other table(i.e. - National), but I need to get the custom value beside it(i.e. - 4)

 

Capture.JPG

 

Let me know if you need any further clarification.

 

 

v-haibl-msft Super Contributor
Super Contributor

Re: How to use IF statement and show the values taken from another source.

@abhishekpati86

 

Please try to create a calculated column with following formula in Barcelona table.

ScoreTrade_Update = 
CALCULATE (
    MAX ( Keyword[Score] ),
    FILTER (
        ALL ( Keyword ),
        FIND (
            UPPER ( Keyword[Keyword] ),
            UPPER ( Barcelona[End User Trade Name] ),
            ,
            0
        )
            > 0
    )
)

How to use IF statement and show the values taken from another source_1.jpg

 

Best Regards,

Herbert