Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
hgalfre
Helper I
Helper I

Date filtering and relationship

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: 

hgalfre_0-1637146208774.png

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. 😅

hgalfre_1-1637146755929.png

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!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1638179991714.png

The final output is shown below:

vyalanwumsft_1-1638180012748.pngvyalanwumsft_2-1638180024009.png

 

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

vyalanwumsft_3-1638180135645.png

Then create a measure.

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

vyalanwumsft_8-1638180635592.png

 

The final output is shown below:

vyalanwumsft_5-1638180243818.pngvyalanwumsft_7-1638180267174.png
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.

 

View solution in original post

9 REPLIES 9
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1638179991714.png

The final output is shown below:

vyalanwumsft_1-1638180012748.pngvyalanwumsft_2-1638180024009.png

 

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

vyalanwumsft_3-1638180135645.png

Then create a measure.

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

vyalanwumsft_8-1638180635592.png

 

The final output is shown below:

vyalanwumsft_5-1638180243818.pngvyalanwumsft_7-1638180267174.png
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.

 

Thanks for your quick reply! it seems to be working

hgalfre
Helper I
Helper I

Ok thanks 🙂 

Here is a wetransfer link :

https://we.tl/t-RFQvLWKtrO

 

v-yalanwu-msft
Community Support
Community Support
hgalfre
Helper I
Helper I

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
Helper I
Helper I

Hi @v-yalanwu-msft 

have you seen my reply to your message?

Thanks for your help 🙂

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
Helper I
Helper I

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:

hgalfre_0-1637570694001.png

 

 

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. 

 

hgalfre_1-1637570743740.png

 

 

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. 

hgalfre_2-1637570765978.png

 

 

All my relationships are bidirectionary and active. 

 

Would you know how to solve this?

 

Thanks!

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1637547281661.png

The final output is shown below:

vyalanwumsft_1-1637547353529.png

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.