Will the related function work if there is relationship between employee table and training table but, the relationship is inactive? I mean to say will related function work on inactive relations?
Could you show the difficult scenarios of lookup function how to use where you have duplucate records in both the tables, instead of sharing simple scenario which is already there in you tube channel..
When working with duplicate records in both the lookup table and the data table, the VLOOKUP function in Excel can return unexpected results, as it will default to the first matching value it finds. To address this, you can use several approaches: 1. Adding a unique identifier column: This involves creating a unique identifier column in the lookup table that combines the values in the columns you intend to use for lookup. This can be done using concatenation or other methods. Then, use VLOOKUP with this unique identifier column as the lookup value. 2. Using the MATCH and INDEX functions: The MATCH function can be used to find the row number of the matching value in the lookup table, and then the INDEX function can be used to retrieve the corresponding value from another column. This approach allows you to specify which match to retrieve, even if there are duplicates. 3. Using the CHOOSE function: The CHOOSE function can be used to select a value from a list based on an index. By combining MATCH and CHOOSE, you can select the desired value from multiple matches in the lookup table. 4. Using a pivot table: Pivot tables can be used to aggregate data and make it easier to extract specific values. By creating a pivot table from the lookup table, you can filter on the desired values and extract the corresponding data. 5. Using advanced formulas or macros: In more complex scenarios, you may need to use advanced formulas or macros to handle duplicate values effectively. This could involve using array formulas, custom functions, or VBA programming. Here's an example using the MATCH and INDEX functions to handle duplicate values in both tables: Data: | Lookup Table | |---|---| | Product | Price | | A | 10 | | B | 20 | | C | 30 | | A | 40 | | Data Table | |---|---| | Product | Quantity | | A | 2 | | B | 5 | | C | 3 | | A | 1 | Formula: =INDEX(lookup_table, MATCH(data_table[1,1], lookup_table[1:4,1], 0), 2) Explanation: The MATCH function searches for the value in cell A1 of the Data Table within the range A1:A4 of the Lookup Table and returns the row number of the first match. The INDEX function retrieves the value from the second column (B) of the Lookup Table at the row number returned by MATCH. This formula will correctly return the price for each product in the Data Table, even if there are duplicate product names in the Lookup Table. Additional tips: Consider sorting the lookup table by the column you intend to use for lookup to improve performance. Be aware of the limitations of lookup functions, such as the inability to handle non-numeric values or large datasets. Choose the appropriate method based on the complexity of your data and the specific requirements of your analysis.
Well Explained 👍
Thanks a ton, Please share with your friends too, and Subscribe our channel for regular updates
Hi.. very helpful & informative videos.
Make more videos on DAX functions & as well as some Scenario based DAx..
wonder full explanation
Thank you 😊
Good explanation thanks 👍
Thank you so much, Please subscribe our channel for regular updates and it motivates us a lot.
Hello Team,
Can you pls make one video on Dual mode connection How it's work? & Purpose, Draw backs?...
Will the related function work if there is relationship between employee table and training table but, the relationship is inactive? I mean to say will related function work on inactive relations?
No it will not work
@@khushbooyadav9047 ok
Will this be work for many to many relationship
related will work only on 1 to many relationship
what if their is no common column between fact and dimension table? now how would you achieve the same thing????
If there is no common column.. We cannot relate
Could you show the difficult scenarios of lookup function how to use where you have duplucate records in both the tables, instead of sharing simple scenario which is already there in you tube channel..
When working with duplicate records in both the lookup table and the data table, the VLOOKUP function in Excel can return unexpected results, as it will default to the first matching value it finds. To address this, you can use several approaches:
1. Adding a unique identifier column:
This involves creating a unique identifier column in the lookup table that combines the values in the columns you intend to use for lookup. This can be done using concatenation or other methods. Then, use VLOOKUP with this unique identifier column as the lookup value.
2. Using the MATCH and INDEX functions:
The MATCH function can be used to find the row number of the matching value in the lookup table, and then the INDEX function can be used to retrieve the corresponding value from another column. This approach allows you to specify which match to retrieve, even if there are duplicates.
3. Using the CHOOSE function:
The CHOOSE function can be used to select a value from a list based on an index. By combining MATCH and CHOOSE, you can select the desired value from multiple matches in the lookup table.
4. Using a pivot table:
Pivot tables can be used to aggregate data and make it easier to extract specific values. By creating a pivot table from the lookup table, you can filter on the desired values and extract the corresponding data.
5. Using advanced formulas or macros:
In more complex scenarios, you may need to use advanced formulas or macros to handle duplicate values effectively. This could involve using array formulas, custom functions, or VBA programming.
Here's an example using the MATCH and INDEX functions to handle duplicate values in both tables:
Data:
| Lookup Table |
|---|---|
| Product | Price |
| A | 10 |
| B | 20 |
| C | 30 |
| A | 40 |
| Data Table |
|---|---|
| Product | Quantity |
| A | 2 |
| B | 5 |
| C | 3 |
| A | 1 |
Formula:
=INDEX(lookup_table, MATCH(data_table[1,1], lookup_table[1:4,1], 0), 2)
Explanation:
The MATCH function searches for the value in cell A1 of the Data Table within the range A1:A4 of the Lookup Table and returns the row number of the first match.
The INDEX function retrieves the value from the second column (B) of the Lookup Table at the row number returned by MATCH.
This formula will correctly return the price for each product in the Data Table, even if there are duplicate product names in the Lookup Table.
Additional tips:
Consider sorting the lookup table by the column you intend to use for lookup to improve performance.
Be aware of the limitations of lookup functions, such as the inability to handle non-numeric values or large datasets.
Choose the appropriate method based on the complexity of your data and the specific requirements of your analysis.
@@KSRDatavizon Thanks a lot... I actually asked in dax scenarios in power bi.
Thank you Uma!🙏
You're welcome 😊