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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
abhishekpati86
Helper III
Helper III

Getting the value from the excel sheet for 2 different columns and rendering it in power BI

Here is the requirement. I have the below matrix from a dataset containing some text values in End User Trade Name.

 

Capture.JPG

 

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.

 

Capture.JPG

 

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 and can be different for individual fields).See the below screenshot for details 

 

Capture.JPG

 

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 ?

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi sridevi,

 

Because there doesn’t exist any relationshipbetween two tables, to return the value from Keyword_Trade_Name table, you need to join these two tables:

crossjoinTbl = ADDCOLUMNS(CROSSJOIN(Keyword_Trade_Name,Table_Test),"match value",IF(FIND(Keyword_Trade_Name[Trade_Name],Table_Test[End User Trade Name],,0)>0,Keyword_Trade_Name[value],0))

bb.PNG

 

Select columns from this new table to add to table visualization.

cc.PNG

 

If you have any question, please feel free to ask.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi sridevi,

 

Because there doesn’t exist any relationshipbetween two tables, to return the value from Keyword_Trade_Name table, you need to join these two tables:

crossjoinTbl = ADDCOLUMNS(CROSSJOIN(Keyword_Trade_Name,Table_Test),"match value",IF(FIND(Keyword_Trade_Name[Trade_Name],Table_Test[End User Trade Name],,0)>0,Keyword_Trade_Name[value],0))

bb.PNG

 

Select columns from this new table to add to table visualization.

cc.PNG

 

If you have any question, please feel free to ask.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yulgu-msft -- It seems to work perfectly. Thanks for providing a workaround.

 

 

abhishekpati86
Helper III
Helper III

@abhishekpati86

 

Try the following

 

1. Add an Index column to your table containing KeyWord_Trade_Name against each Name starting with 1 and on.

    So yourKeyWord_Trade_Name will have columns Name,Score,Index

2. Create a column in Table_test as follows

   

                                  MatchedIndex=
                                                       SUMX(SearchNames,
                                                                      If (FIND(UPPER(KeyWord_Trade_Name [Name]),
                                                                                        UPPER(Table_test [EndUserName]),,0
                                                                                         ) >0,(KeyWord_Trade_Name [Index])-2999997,9999999 )


                                                                       )

4. This would return the values as 1,2,3.... depending on the match or else 999999.

5. In the Mange relationship create relationship between KeyWord_Trade_Name[Index] and Table_test[MatchedINdex]

6. Create a column in Table_test 

      NewScoreTrade = Related(KeyWord_Trade_Name [Score])

 

7. Now plot the reports with NewScoreTrade , this will be dynamic then.

 

8.  The only thing I am unable to explain why in the If conditions one needs to put the odd numbers like 2999997. This number to subtract varies based on the else value 9999999.  If you change the else value you have to change the 2999997 also accordingly.

 

Try it out if it solves your issue please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

                 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Capture.JPG

 

@CheenuSing - Please see above the MatchedIndex, I have also created the manage relationship as below. Still it seems to not work. Can you suggest what is wrong over here.

 

Capture.JPG

Hi @abhishekpati86

 

Can you create a small sample of Barcelona... Table where the names match with the KeyWord_trade table and check it out.

 

Since your index values are from 0 onwards chenge the 2999997 to 2999998.

 

Let me know if it works.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing - I think the solution provided by @v-yulgu-msft is easy and hassle free. Thanks for your time and helping me around. It is because of contributers like you that the Power BI community is growing leaps and bounds. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.