Date filtering and relationship

[hgalfre]

Hi there! 

I am working on a big report with 24 tables, so it's not easy to manage relationships. 

I have a problem on 3 that are connected, but I can't seem to make the date filter work on the data for 1 of the 3. 

So I have:

- 1 sales table, with many dates. 

- 1 salesmen table, with unique values; 1 code for each salesman + 1 name > it's connected  with a bidirectional filter to the sales table, by the salesman code

- 1 table containing the monthly turnover target for each salesman. So for each one, there are 12 lines, one for each month. > It's connected with a bidirectional filter to the salesmen table through the salesman code. 

I keep the salesmen table and the target table apart as some of the codes listed in the salesmen table are not actual salesmen, so there's no target.

Here the model: 

The two connections are bi-directionary and active obviously.

On my report, I have set up a target visual, and I added the filter "Month" from the sales table as a filter for the wole page, because I would like to see the KPI yearly or monthly.

When I use it, it works well for the turnover (which comes from the same table; sales table) in my KPI visual, as well as for the other visuals, but it does not change the target data (in this case, it's a sum of the monthly target column).

The target would only switch to monthly target if I use the month from the target table. However, the turnover doesn't change accordingly this time. 😅

In my visual, I have used the sales turnover as the indicator, the year (from the sales table) as the trend axis, the column Monthly target as the target. 

Would anyone know what is the problem?

I assume it's a relationship issue, but I don't see what I have done wrong...

Thanks for your help!

[v-yalanwu-msft · Accepted solution]

Hi， @hgalfre ；

There are two methods:
1.One is to create many-to-many relationships between dates in "Actual Sales table" and "Actual Target table", and delete other relationships or make them inactive.

The final output is shown below:

2.The second method is to join the date inactive relationship between the two tables and create the measure using USERELATIONSHIP().

Then create a measure.

sale2 = CALCULATE(SUM('Actual sales table'[Sales]),USERELATIONSHIP('Actual sales table'[Date],'Actual target table'[Date]))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hgalfre]

Thanks for your quick reply! it seems to be working

[hgalfre]

Ok thanks 🙂 

Here is a wetransfer link :

https://we.tl/t-RFQvLWKtrO

[v-yalanwu-msft]

Hi, @hgalfre ;

I think ok.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Best Regards,
Community Support Team_ Yalan Wu

[hgalfre]

Hi @v-yalanwu-msft !

Well I've already created a file that reproduces the issue, but as I said last week, I cannot send it here (I am not allowed to enclose files on the powerbi community). I've tried on a private message, but I don't have the option available either 😕

Is there anyway for you to allow me to send files?

Or is a wetransfer link ok?

Thanks!

[hgalfre]

Hi @v-yalanwu-msft 

have you seen my reply to your message?

Thanks for your help 🙂

[v-yalanwu-msft]

Hi, @hgalfre ;

Sorry for replying to you so late. I have studied for some time, but I cannot reproduce your problem. Could you create a simple file to remove sensitive information? Then explain the logic.

Thank you!

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hgalfre]

Hi Yalan Wu, 

Thnaks for your reply. 

The model you replicated is not correct. 

I cannot send you a file; could you allow me?

Here a preview:

In the meantime, I have also added a date table to my model, to see if it could solve the problem. 

So, in my model, with the Date table added (just like in this example) + the addition of measures for MTD target, I managed to get the KPI visual work. It now shows only the MTD target and not the full target. 

However, in the enclosed example model, it doesn't work. If you get rid of "december", the target data changes. But it shouldn't as we're still in november. You'll see that I created two visuals; one with the MTD target measure, and one with the MTD target. 

But that's not too preoccupying as I made it work in my real model.

However, you'll see that I tried to create a table visual that gives the sum of all salesmen monthly target per month (so basically it would give the total for all the sales team for each month). It's made with the target column and the month from the date table. But it doesn't work. 

All my relationships are bidirectionary and active. 

Would you know how to solve this?

Thanks!

[v-yalanwu-msft]

Hi， @hgalfre ；

I used a simple model to test, there are two points to pay attention to, the first is to keep the relationship bidirectional, and keep the relationship active, if the relationship is dotted line need to activate the relationship with DAX. and are these two relationships one to many?
This works in my example:

The final output is shown below:

You mentioned that there are 21 tables, I am not sure if there are other factors, if it is ok in the case of bidirectional relationship and activation of and one-to-many, you can check it, if it is still not correct, please share more details.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.