Forum Discussion

abhishekpati86's avatar
abhishekpati86
Helper III
9 years ago
Solved

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.     The 3rd column contains a score and it is set to 1 for a selected grou...
Need Help
Show and Tell
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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))

     

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

     

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

     

    Best regards,
    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

 

                 

 

  • abhishekpati86's avatar
    abhishekpati86
    Helper III
    9 years ago

     

    Anonymous - 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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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

       

      • abhishekpati86's avatar
        abhishekpati86
        Helper III
        9 years ago

        Anonymous - 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.