Forum Discussion
Incorrect count results
I have three tables in the relationship as follows.
Date
Account
ItemDetail
Date.Date (1:Many) Account.AccountDate (inactive relation)
Date.Date (1:Many) Itemdetail.AccountDate (inactive relation)
Date.Date (1:Many) Itemdetail.itemPurchasedate (inactive relation)
Account.ID (1:Many) Itemdetail.AccountID (active relation)
Account Table
| AccountID | AccountDate | AccountName |
| AC123 | 1/1/2020 | HPC |
| AC234 | 1/10/20 | MBC |
| AC345 | 1/11/20 | FED |
| AC456 | 1/15/20 | DEF |
| AC567 | 1/16/20 | MND |
ItemDetail Table
| ItemID | ItemName | AccountID | AccountDate | Purchasestatus | PurchaseDate |
| IT678 | Shoes | HPC | 1/1/20 | Complete | 2/1/20 |
| IT667 | Belts | HPC | 1/1/20 | Hold | 1/10/20 |
| IT567 | Masks | HPC | 1/1/20 | Complete | 1/20/20 |
| IT777 | Tees | HPC | 1/1/20 | Complete | 1/20/20 |
| IT879 | Watch | HPC | 1/1/20 | Complete | 1/15/20 |
| IT444 | Band | HPC | 1/1/20 | Schedule | 1/10/20 |
The Goal is to create a multirow card with counts from the Account and the Item table, but based of the Account date. Now i am able to get the data for Account data, but the counts from the item table are incorrect. Not sure what am i missing.
For January Counts
Account Counts : calculate(counta(Accountid), userelationship (Date.Date, Account.Accountdate)) - This gives me 5
Item Counts: Calculate(counta(Itemid), filter(Itemdetail, purchasestatus = "Complete"), userelationship (date.date, itemdetail.accountdate) - this gives me 2, rather than 4.
Please advise what am i missing.
2 Replies
Oddly.. if i change the Item Counts forumula to
Calculate(counta(Itemid), userelationship (date.date, itemdetail.accountdate) ) i get the complete row count which is correct as per the raw table, but i need just the complete.
Hi PBI5851 ,
Does cross filter direction is both? I get the correct results with your formula.
Can you please share a few screenshots about your situation? Then we will understand more clearly.
