Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhishekpati86
Helper III
Helper III

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

@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

View solution in original post

6 REPLIES 6
v-haibl-msft
Employee
Employee

@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

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.

 

 

@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

abhishekpati86
Helper III
Helper III

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

abhishekpati86
Helper III
Helper III

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.