cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Userelationship() and filtering from fact table

Hello, I have a start schema data model, date table and fact table have multiple inactive relationships, one of them is active. In fact table I have data about ticket: ticketid, creationDate, closeDate etc. CreationDate is active relationship, closeDate is inactive relationship. I need to have slicers YearMonth (coming from Date table) and TicketID (coming from fact table). If I filter, YearMonth=202005, the result of measure is 1 CALCULATE(COUNTROWS(Fact), USERALATIONSHIP(Fact[CloseDate],Date[Date]), which is correct But in the filters for ticketID I see nothing. How to set this up to have ticketid=234 in the slicer?

 

Source:

ticketId | creationDate | closeDate

123 | 02.02.2020 | 04.04.2020

234 | 03.04.2020 | 05.05.2020

 

Result:

YearMonth| TicketID |Close

202005 |234|1

 

Any thoughts?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Userelationship() and filtering from fact table

Hi @Cactus266 ,

 

Because you have active relationship between fact table and date table. Once you select 202005 in slicer there's no data have creation date in 202005, so the ticket slicer will be blank.

If you want the slicer won't be affected by yearmonth slicer, you can edit the interaction by select the yearmonth slicer and click edit interaction feature under Format menu.

2.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Userelationship() and filtering from fact table

@Cactus266 , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Just check dates are identified as the date and have no timestamp, Change format to include time and check.

 

If it has a timestamp, then create a date filed like given below and use that in join

 

Close = [close date].date



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Re: Userelationship() and filtering from fact table

@amitchandak, it is just date column type

Highlighted
Super User IV
Super User IV

Re: Userelationship() and filtering from fact table

@Cactus266 , are you getting data when there is no filter with year



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Re: Userelationship() and filtering from fact table

 

@amitchandak

Here how does it look in Power BI, I am receiving expected results. But tickets are gone in TicketID filter.

Capture.PNG

Highlighted
Super User IV
Super User IV

Re: Userelationship() and filtering from fact table

@Cactus266 , create a separate dimension/table for Ticket No. or diable interactions 

https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Re: Userelationship() and filtering from fact table

@amitchandak, creating separate dimension table just for ticketId will duplicate the data. What means diable interaction?

I need to filter by ticketId.

Highlighted
Community Support
Community Support

Re: Userelationship() and filtering from fact table

Hi @Cactus266 ,

 

Because you have active relationship between fact table and date table. Once you select 202005 in slicer there's no data have creation date in 202005, so the ticket slicer will be blank.

If you want the slicer won't be affected by yearmonth slicer, you can edit the interaction by select the yearmonth slicer and click edit interaction feature under Format menu.

2.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors