Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
zigune
Frequent Visitor

Filter a period on a slicer with inactive relation

‎07-07-2022 06:57 AM

Hello,

 

I have 3 tables :

 

- Period which is a set of dates, and monthes

 

- RequestsHours who has an active relationship with the period table base on the "date" field and another field "total"

 

- HoursDone who can only have an inactive relationship with the period table (on the "date" field") because it already has another active relationship with other tables linked to the Period table and a "total" field

 

 

I want to do a chart with a comparison of the RequestedHours (total) and HoursDone (total) for a particular Month based on a slicer on the month field.

 

The problem is my slicer only filters requested hours and not hours done. I tried several measures with "USERELATIONSHIP" with no success. Can somebody help me with this problem ? What is the real solution ?

 

Thank you in advance for your help.

Labels:
Message 1 of 6
574 Views
0
5 REPLIES 5
zigune
Frequent Visitor

‎07-11-2022 02:27 AM

Hello,

 

it's because I masked some tables from my diagram... (I have a lot of tables...) Here it is :

 

zigune_0-1657531605505.png

 

zigune

Message 5 of 6
498 Views
0
PaulDBrown
Community Champion
Community Champion
In response to zigune

‎07-11-2022 03:41 AM

The problem is the Many-to-many bi-directional filter between the HoursDone table and the TP_projects table. The relationship between the period table and the Hours Done table cannot be active because it could create conflicting filtering as the filter propagates arpund the tables:

relat.png

 

You can solve this by creating a bridge table with unique values for the Code Project field, and create single-to-many relationships allround:

relat 1.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Message 6 of 6
489 Views
0
PaulDBrown
Community Champion
Community Champion

‎07-07-2022 07:15 AM

Can you please provide sample data?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Message 2 of 6
567 Views
0
zigune
Frequent Visitor
In response to PaulDBrown

‎07-11-2022 12:07 AM

Hello,

 

thank you for your answer :

 

Here are sample data.

 

RequestsHours :

 

DateTotal
01.02.2022 00:002
01.02.2022 00:001.35
02.02.2022 00:002
02.02.2022 00:001.35
03.02.2022 00:002
03.02.2022 00:001.35
04.02.2022 00:002
04.02.2022 00:001.35
07.02.2022 00:002
07.02.2022 00:001.35
08.02.2022 00:002
08.02.2022 00:001.35
09.02.2022 00:002
09.02.2022 00:001.35
10.02.2022 00:002
10.02.2022 00:001.35
11.02.2022 00:002
11.02.2022 00:001.35

 

HoursDone :

 

datetotal
03.01.2022 00:002.5
04.01.2022 00:004
05.01.2022 00:009
06.01.2022 00:0011.5
07.01.2022 00:0011
10.01.2022 00:002.5
11.01.2022 00:009.5
12.01.2022 00:002
13.01.2022 00:009
14.01.2022 00:007.5
17.01.2022 00:0014.5
18.01.2022 00:006
19.01.2022 00:003
20.01.2022 00:0013
21.01.2022 00:008
24.01.2022 00:0012.25
25.01.2022 00:0012.5
26.01.2022 00:007
27.01.2022 00:0014

 

Period :

 

DateYear-Month
01.01.2022 00:002022-01
02.01.2022 00:002022-01
03.01.2022 00:002022-01
04.01.2022 00:002022-01
05.01.2022 00:002022-01
06.01.2022 00:002022-01
07.01.2022 00:002022-01
08.01.2022 00:002022-01
09.01.2022 00:002022-01
10.01.2022 00:002022-01
11.01.2022 00:002022-01
12.01.2022 00:002022-01
13.01.2022 00:002022-01
14.01.2022 00:002022-01
15.01.2022 00:002022-01
16.01.2022 00:002022-01
17.01.2022 00:002022-01
18.01.2022 00:002022-01
19.01.2022 00:002022-01
20.01.2022 00:002022-01
21.01.2022 00:002022-01
22.01.2022 00:002022-01
23.01.2022 00:002022-01
24.01.2022 00:002022-01
25.01.2022 00:002022-01
26.01.2022 00:002022-01
27.01.2022 00:002022-01
28.01.2022 00:002022-01
29.01.2022 00:002022-01
30.01.2022 00:002022-01
31.01.2022 00:002022-01
01.02.2022 00:002022-02
02.02.2022 00:002022-02
03.02.2022 00:002022-02
04.02.2022 00:002022-02
05.02.2022 00:002022-02
06.02.2022 00:002022-02
07.02.2022 00:002022-02
08.02.2022 00:002022-02

 

Best regards :

 

zigune_0-1657523194220.png

 

Message 3 of 6
524 Views
0
PaulDBrown
Community Champion
Community Champion
In response to zigune

‎07-11-2022 01:46 AM

Thanks for the sample data. Ok, some questions:

-RequestsHours who has an active relationship with the period table base on the "date" field and another field "total"
-HoursDone who can only have an inactive relationship with the period table (on the "date" field") because it already has another active relationship with other tables linked to the Period table and a "total" field

Sorry, which relationship are these? They are not in the model you depicted.

 

BTW, when dealing with dates, it is better if you format all the date fields as "Date" (Not "Date/time")

 

This is the model I have so far:

model.png

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Message 4 of 6
519 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All