Forum Discussion
Advocate IUsing a slicer to filter on articlegroup
It sounds like there is an issue with the relationships between Revenue 2014/2016 and the date table. Because all of the revenue tables have a many to 1 relationship to the articles table and a many to 1 relationship to the date table, there can only be one active relationship all the way from Articles table to date table. You can tell if a relationship is inactive in the model by going to the relationship view and the relationship line will be dotted instead of solid. Here is an example of this when I created a model with your schema:
To solve this issue you can either merge the Revenue 2014/2015/2016 tables into one revenue table so that there is only one relationship between Articles, Revenue and Date. Or you can write the measures for Revenue 2014 and Revenue 2016 so that they explicitly use the inactive relationships. This is done using the USERELATIONSHIP() function. It should look close to this for your model:
Revenue 2014 = CALCULATE(SUM('Revenue 2014'[revenuegenerated]), USERELATIONSHIP('Revenue 2014'[date], 'Date'[Date])) Revenue 2016 = CALCULATE(SUM('Revenue 2016'[revenuegenerated]), USERELATIONSHIP('Revenue 2016'[date], 'Date'[Date]))
Advocate IVIt sounds like there is an issue with the relationships between Revenue 2014/2016 and the date table. Because all of the revenue tables have a many to 1 relationship to the articles table and a many to 1 relationship to the date table, there can only be one active relationship all the way from Articles table to date table. You can tell if a relationship is inactive in the model by going to the relationship view and the relationship line will be dotted instead of solid. Here is an example of this when I created a model with your schema:
To solve this issue you can either merge the Revenue 2014/2015/2016 tables into one revenue table so that there is only one relationship between Articles, Revenue and Date. Or you can write the measures for Revenue 2014 and Revenue 2016 so that they explicitly use the inactive relationships. This is done using the USERELATIONSHIP() function. It should look close to this for your model:
Revenue 2014 = CALCULATE(SUM('Revenue 2014'[revenuegenerated]), USERELATIONSHIP('Revenue 2014'[date], 'Date'[Date]))
Revenue 2016 = CALCULATE(SUM('Revenue 2016'[revenuegenerated]), USERELATIONSHIP('Revenue 2016'[date], 'Date'[Date]))
Advocate IThank you very much for your answer!
Besides the solution you gave me (which worked perfectly), it also gave me some great newer insights in how the relations in the PowerBI data model work!
I indeed had my data model in such a way you also created and found that indeed 2 ouf of 3 relations were inactive (didn't even notice/know this). By creating the measurements you described, I have been able to filter on articlegroup perfectly for each year.
Kudos to you ;)