Userelationship() and filtering from fact table

[Cactus266]

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?

[amitchandak]

@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

[Cactus266]

@amitchandak, it is just date column type

[amitchandak]

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

[Cactus266]

@amitchandak

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

[amitchandak]

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

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

[Cactus266]

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

I need to filter by ticketId.

[v-jayw-msft · Accepted solution]

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.

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.