Typically One Side Table is your LookUp Table. That is to say….Normally we lookup or pull a text value from One Side into a Many Side Table
Pulling a value from One Side Table into a Many Side Table is quite simple. It is similar to VLOOKUP in excel
As a calculated Column we can simply use
Column= RELATED(OneSideTable[DesiredColumnName])
OR
Column= Calculate(VALUES(OneSideTable[DesiredColumnName]))
Using a MEASURE, it is even simpler. You do no need the CALCULATE since FILTER context is already present
MEASURE=VALUES(OneSideTable[DesiredColumnName])
In some circumstances, we need the opposite. An example will make it clear
Suppose we have this data Model
And you want to add a Calculated Column in the “Products” (One Side) Table that will give you the Season of Sales of these Products. So the Final Output would look like
The “”Related”..DAX function doesn’t work on ONE SIDE….
Calculate and VALUES combination given below would give an error of Multiple Values Supplied….why…because for each Fruit in Products table there are 2 corresponding rows in Sales Table. The formula cannot determine which value it should pull.
Season=CALCULATE(VALUES(Sales[Season]))
SOLUTION
One Solution is to use FirstNonBlank or LastNonBlank
Season=CALCULATE(FIRSTNONBLANK(Sales[Season],1))
Season=CALCULATE(LASTNONBLANK(Sales[Season],1))
The results are as follows
Apples and Bananas were sold in both WINTER and SUMMER seasons…..LastNonBlank and FirstNonBlank pull only one Value.
A better solution is provided by CONCATENATEX function which takes all the possible TEXT values and concatenate them together.
Season =CALCULATE(CONCATENATEX(sales,Sales[Season],", "))
This will give us the desired output
Click here to Download Excel file and look at the Data Model