Selecting values from Userelationship returns blank results

[zenmemo]

Hi Community,

I'm currently working with direct query and my current setup involves two tables. One active relationship between the Dates table and the Identified Dates and another inactive relationship between Dates and Closed Dates. My problem is with the latter...

When I select values for the active relationship (Count by Identified Date) it returns the corresponding data set.

For the inactive relationship, I was able to get the userelationship function working correctly in that it returns the correct values in my matrix table. My formula:

Count by Closed Date = CALCULATE([Count by Identified Date],
'Issues'[Status]="Closed",
USERELATIONSHIP('Issues'[Closed Date Clean],Dates[value]))

However, when I go and click on the values in my matrix table for the Count by Closed Date, it doesn't return the values in my corresponding table. I'm definitely missing something here and need help.

Thank you very much in advance.

Will

[ChandeepChhabra]

@zenmemo, Can you share your pbi file?

[zenmemo]

https://purplebi-my.sharepoint.com/:u:/g/personal/william_purplebi_onmicrosoft_com/EQr50z6qOitMjuYqo...

[zenmemo]

@ChandeepChhabra, Thanks!

https://purplebi-my.sharepoint.com/:u:/g/personal/william_purplebi_onmicrosoft_com/ETBtQZ6ttRJFpmvKa...

[ChandeepChhabra]

@zenmemo Sorry not able to download the file, the link retuns an error

[zenmemo]

https://purplebi-my.sharepoint.com/personal/william_purplebi_onmicrosoft_com/_layouts/15/onedrive.as...

[ChandeepChhabra]

Still gives an error, can you share the file via drop box or google drive

[zenmemo]

https://drive.google.com/open?id=12q4j9n8a2EIgiuEjFucrz4OboNSWu-aX

alright. hope this works.

[ChandeepChhabra · Accepted solution]

@zenmemo I think the cross filtering happens basis on the active relationship. That could be the reason why your table is blank when you click on [Count by Closed Date]

Here is a quick workaround. Add a dummy measure

Dummy = IF(OR([Count by Closed Date]<>BLANK(),[Count by Identified Date]<>BLANK()),1,BLANK())

Then add this into your table as a last column, it should now work. Here is the snapshot

You can download the powerbi file here

Hope it helps

[zenmemo]

wow. that's a quite clever workaround. Thanks @ChandeepChhabra!

[ChandeepChhabra]

Thanks @zenmemo, Glad it worked

[zenmemo]

https://purplebi-my.sharepoint.com/:u:/g/personal/william_purplebi_onmicrosoft_com/ETBtQZ6ttRJFpmvKa...

Thanks @ChandeepChhabra!