Forum Discussion

Helper III

9 years ago

Solved

Get the values from an excel rather than hardcoding

Here is the requirement. I have the below matrix from a dataset containing some text values in End User Trade Name. The 3rd column contains a score and it is set to 1 for a selected group ...

Need Help

Show and Tell

v-ljerr-msft
9 years ago
abhishekpati86
I am hardcoding the value to be 1(in bold above) in this case here but I don't want it to do that way. I want it to be taken from an excel file and the value can be set by the user (maybe 2 , 3 but same for all the keywords in that table).
According to your description, you should be able to use the same excel sheet "Keyword_Trade_Name" to add another column called Score with the value like below.
Then use MAX function which returns the largest numeric value in a column(as the Score value is the same for all the keywords in that table, it will return that value) like below to calculate "ScoreTrade" for "End User Trade Name".
ScoreTrade = IF ( SUMX ( Keyword_Trade_Name, FIND ( UPPER ( Keyword_Trade_Name[Trade_Name] ), UPPER ( Table_Test[End User Trade Name] ), , 0 ) ) > 0, MAX ( Keyword_Trade_Name[Score] ), 0 )
Regards

v-ljerr-msft

Microsoft Employee

9 years ago

abhishekpati86

I am hardcoding the value to be 1(in bold above) in this case here but I don't want it to do that way. I want it to be taken from an excel file and the value can be set by the user (maybe 2 , 3 but same for all the keywords in that table).

According to your description, you should be able to use the same excel sheet "Keyword_Trade_Name" to add another column called Score with the value like below.

Then use MAX function which returns the largest numeric value in a column(as the Score value is the same for all the keywords in that table, it will return that value) like below to calculate "ScoreTrade" for "End User Trade Name".

ScoreTrade =
IF (
    SUMX (
        Keyword_Trade_Name,
        FIND (
            UPPER ( Keyword_Trade_Name[Trade_Name] ),
            UPPER ( Table_Test[End User Trade Name] ),
            ,
            0
        )
    )
        > 0,
    MAX ( Keyword_Trade_Name[Score] ),
    0
)

Regards