Forum Discussion
Date relationship issues
Hi. I have this weird issue with dates. I have an accounts table that is related to the Account[Date Opened] *-->1 _date[Date] field in the _date table. The relationship is inactive. However, I activate the relationship in measures as needed (see the measure down below, please). Here is the problem. I know that I should have 328 accounts in November that meet the criteria of the measure (defined down below.) How I know? I query SQL for the same criteria and get 328 for November. I also have a test table visual using the Account[Date Opened] instead of using the relationship with the _date[Date] to restrict to only Nov., 2025. And that, too, gives me 322. Instead of 328, I get 322 in the table on the right that uses the _date[Date] filter. I determined that the 6 missing from the table that is using the _date[date] column are the totals for the last two days of Nov 2025. What is weirder is that, once I deselect the 30th of Nov from the _date[Date] filter on the table, everything is gone. See the second illustration with the red note.
Could you please try this:
Traded Accts D7 = CALCULATE( DISTINCTCOUNT(account[Account Number]), USERELATIONSHIP(_date[Date], account[Date Opened]), CROSSFILTER(account[Account Number], Assets[Account Number], NONE), FILTER( account, NOT ISBLANK(account[First Trade Date]) && account[First Trade Date] <= account[Date Opened] + 7 )
10 Replies
Please try whit COUNTROWS DISTINCTCOUNT(account) instead of DISTINCTCOUNT(account[Account Number])
I hope this helps. if so please mark it as a solution. Kudos are welcome.Thanks so much. I tried (below), but I unfortunately still got the same wrong number (322) instead of 328.
Traded Accts D7 =CALCULATE(COUNTROWS(account),USERELATIONSHIP(_date[Date], account[Date Opened]),FILTER(account,NOT ISBLANK(account[First Trade Date]) &&account[First Trade Date] <= account[Date Opened] + 7))Could you please try this:
Traded Accts D7 = CALCULATE( DISTINCTCOUNT(account[Account Number]), USERELATIONSHIP(_date[Date], account[Date Opened]), CROSSFILTER(account[Account Number], Assets[Account Number], NONE), FILTER( account, NOT ISBLANK(account[First Trade Date]) && account[First Trade Date] <= account[Date Opened] + 7 )
Here is an update. I tried to activate the relationship between the Account[Date Opened] and _date[Date]. That gave me an error "multiple paths ... " and mentioned the other path as being through the Assets[Account Number] and the Account[Account Number] which is an active relationship. I then, just for the sake of testing, made this relationship inactive and then was able to make the account[Date Opened] and _date[Date] active. that immediately solved the problem. I now get 328. Does anyone know what was happening. I mean, I can't just leave the relationship between Account and Assets inactive because that will most likely break other measures. Thanks.
Second update: it seems that when I changed the crossfilter between the Account and the Assets tables from both to single that that fixed the problem too without having to deactivate this relationship.
Hi,
Share the download link of the PBI file.
Hi. This is a direct-query complex power bi report. It's not always possible to share the pbi files here. I was hoping that someone here had encountered a similar problem. I'm going to try to create a separate file with some sample extracts from the same tables and see if i can do that. Thanks.
Here is an update. I tried to activate the relationship between the Account[Date Opened] and _date[Date]. That gave me an error "multiple paths ... " and mentioned the other path as being through the Assets[Account Number] and the Account[Account Number] which is an active relationship. I then, just for the sake of testing, made this relationship inactive and then was able to make the account[Date Opened] and _date[Date] active. that immediately solved the problem. I now get 328. Does anyone know what was happening. I mean, I can't just leave the relationship between Account and Assets inactive because that will most likely break other measures. Thanks.
Second update: it seems that when I changed the crossfilter between the Account and the Assets tables from both to single that that fixed the problem too without having to deactivate this relationship.
Here is an update. I tried to activate the relationship between the Account[Date Opened] and _date[Date]. That gave me an error "multiple paths ... " and mentioned the other path as being through the Assets[Account Number] and the Account[Account Number] which is an active relationship. I then, just for the sake of testing, made this relationship inactive and then was able to make the account[Date Opened] and _date[Date] active. that immediately solved the problem. I now get 328. Does anyone know what was happening. I mean, I can't just leave the relationship between Account and Assets inactive because that will most likely break other measures. Thanks.
