USERELATIONSHIP is a DAX function that you may use if you have more than one relationships between two tables. This is especially applicable for two dates in your transactions/fact table and one calendar table. See example in the video below:
Tag: Excel
How to do Lookup in Excel and Power Query
Lookup in regular Excel function
The XLOOKUP
function has been available to Office 365 and Excel 2021. For compatibility purposes with my workbook’s potential users, I shy away from using this function. Instead, I use the combination of INDEX
and MATCH
.
Why not VLOOKUP?
I’m not sure if there are any hardware efficiency benefit from using INDEX
and MATCH
over VLOOKUP
or HLOOKUP
. But I use INDEX
and MATCH
primarily for two reasons:
- Ability to return the value of the column to the left of your lookup array (left-most column of the table array)
- Never worry about adding a column between the returned column and the lookup array. Remember, you indicate the column index number being the returned column from your table array? What if you’ve not touched your file for a long time, return to it and inserting a column in a table, forgetting that you’ve used that table as a table array as a lookup somewhere in your worksheet? You’ll certainly break your
VLOOKUP
formula.
How to use these formulas
Sorry, but you have to look somewhere else for the usage of these formulas.
Lookup in Power Query
Power Query Lookup Sample File
To better demonstrate the functions, it’s better if we refer to a sample file:
Continue reading
Sometimes it pays to keep Index and Match to yourself
To me, Index and Match functions combined is so much better than VLookUp. But sometimes, that fact is better kept to myself rather than trying to convert others. Saves me a lot of headache.
Please, don’t get me started on XLOOKUP. Compatibility issues is a serious concern.
So, are you Team Index and Match or Team VLookUp?
iPad and iPadOS as a computer
It may be for Mac users. Not for me. Unless, it can run Power Query and Power PIvot on its Excel app.