Using a slicer to filter on articlegroup

[Niels_NL]

Hello people,

Before I'll explain my problem, here's the datasets (incl. relevant attributes) I'm working with:

• Revenue 2014 table (date, articlecode, groupcode, groupname, revenuegenerated)
• Revenue 2015 table (date, articlecode, groupcode, groupname, revenuegenerated)
• Revenue 2016 table (date, articlecode, groupcode, groupname, revenuegenerated)
• Articles table (articlecode, articleprice, groupcode, groupname)
• Articlegroups table (groupcode, groupname)

'Revenue 2014/2015/2016' are linked to 'Articles' on the articlecode only.

'Articles' is linked to 'Articlegroups' on the groupcode only.

I also got a seperate date table to filter on months, but this is working well as it filters perfectly on year and month.

My problem:

I want to create a barchart with revenue per month. Next, I want to add a slicer for 'groupnames' that filters this barchart, so that revenue per month for that particular articlegroup is shown.

As input for the slicer, I'm using the 'groupname' from the Articlegroups table.

The problem is that it does show this for the year 2015, however not for 2014 and 2016.

For 2014 and 2016 it still shows the total revenue for that month, instead of for that month and for that articlegroup.

Screenshot of barchart + slicer:

PH1_Naam = groupname

Omzet_2014/2015/2016 = revenue 2014/2015/2016

Any help why this problem is occuring (and possibly a solution) would be greatly appreciated!

Thanks,

Niels

[Twan · Accepted solution]

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]))

[Niels_NL]

Thank 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 😉