Create relationship between measures and column from another table

[DJOXXXA]

Hi folks,

I ran into a problem when trying to display icons for weather forecast in my PBI dashboard. To give you a bit more detail - I need to show icons for current week (which is selected in the slicer), last week and same week last year (these two are measures), and I have successfully imported the icons, but the problem is that the icons are only related to the weather column for current week. Here is the relationship overview:

As you can see, only relationship is between column Weather (for selected week) and Weather from the Weather Icons table (in that table is the unique list of weather descriptions and icon URLs). My question is, how can I link the measures Weather Last Week and Weather Last Year to the Weather Icons table?

As things stand now, weather icons are not showing properly when either Weather Last Week or Weather Last Year are displayed on a visual:

Is there a solution for this problem? I would kindly appreciate if somebody could point me in the right direction...

Best regards!

[Mariusz]

Hi @DJOXXXA 

What method do you use to filter last week / last year, is dax measure or relative date filtering?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DJOXXXA]

Hi @Mariusz,

Due to the requirement to compare Monday from this week with Monday from the last week or with Monday from the same week last year, I have introduced additional helper columns in my calendar, in order to calculate these values as easily as possible.

So, measure for the Weather last week looks like this:

Weather Last Week = CALCULATE(VALUES(Weather[Weather]);
FILTER(ALL('Calendar');'Calendar'[LW Helper Column] = MAX('Calendar'[LW Helper Column]) -1) )

and the LW Helper Column is a nothing more than a calculated column:

LW Helper Column = Weekday * 1000000 + Cumulative Week

I know this is not "by the book" solution, but I havent found any other way to match weeks on weekday basis. Almost same measure formula is used for Last year calculation, but with different helper column (just replace LW Helper with LY Helper):

LY Helper Column = Weekday * 1000000 + Week * 10000 + Year

[Mariusz · Accepted solution]

Hi @DJOXXXA 

You can create another Measure for previous week, year icon using same logic as you did to return the description or use something like below.

PrevIcon = 
VAR _prev = { [PrevDesc] }
RETURN 
CALCULATE(
    SELECTEDVALUE( icons[Icon] ),
    ALL( icons ),
    TREATAS( _prev, icons[Desc] )
)

This measure will use the result of your current measures and find the corresponding icons, like below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DJOXXXA]

Dear @Mariusz, thank you very much, you are a genius! It is working perfectly 🙂