The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 of texts that is coming from another excel file.
I have a table named the same in my Report and when the text matches it to the one in the list, the score is set to 1 for that entry. The excel is for the purpose that anyone can enter the values and just have to do a refresh in the Power BI to update the score for those set of values.
Below is the code that is doing it -
ScoreTrade = IF(
SUMX(Keyword_Trade_Name,
FIND(
UPPER(Keyword_Trade_Name[Trade_Name]),
UPPER(Table_Test[End User Trade Name])
,,0
)
) > 0,
1,
0
)
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). Can you suggest a way to do that so that I don't have to hardcode it but maybe use the same excel sheet to add another column with the value ?
Solved! Go to Solution.
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
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 of texts that is coming from another excel file.
I have a table named the same in my Report and when the text matches it to the one in the list, the score is set to 1 for that entry. The excel is for the purpose that anyone can enter the values and just have to do a refresh in the Power BI to update the score for those set of values.
Below is the code that is doing it -
ScoreTrade = IF(
SUMX(Keyword_Trade_Name,
FIND(
UPPER(Keyword_Trade_Name[Trade_Name]),
UPPER(Table_Test[End User Trade Name])
,,0
)
) > 0,
1,
0
)
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). Can you suggest a way to do that so that I don't have to hardcode it but maybe use the same excel sheet to add another column with the value ?
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
Hi @v-ljerr-msft,
Thanks for the solution, now my Director wants it to be more dynamic.
So now the data should look something like this
Is there a way to extract the possible values in the above manner ?
Thanks a lot for your help.
Hi All,
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 of texts that is coming from another excel file.
I have a table named the same in my Report and when the text matches it to the one in the list, the score is set to 1 for that entry. The excel is for the purpose that anyone can enter the values and just have to do a refresh in the Power BI to update the score for those set of values.
Below is the code that is doing it -
ScoreTrade = IF(
SUMX(Keyword_Trade_Name,
FIND(
UPPER(Keyword_Trade_Name[Trade_Name]),
UPPER(Table_Test[End User Trade Name])
,,0
)
) > 0,
1,
0
)
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). Can you suggest a way to do that so that I don't have to hardcode it but maybe use the same excel sheet to add another column with the value ?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |