Forum Discussion
Post ProdigyKPI Card showing incorrect total
Dear All – I need your help with the count function in the KPI card.
When I select the year 2015 in my report, the KPI card doesn’t filter/change the count whereas the matrix filters it correctly. In .the below image, the matrix shows only 1 client but the KPI cards show 2.
Here is the PBIX file. Click Here
You are doing absolutely right using the slicer and card for the count. It should give 1 and rather shoving two.
PFA the updated file. It doesn't use a new measure.
Since the data has only one record in table ARInvoices for 2015 you see the metrix shows 1.
Now the KPI shows 2 because it has two records in ClientMaster.If there were 100 records in ClientMaster it would show 100.
Why is this happening because 2015 slicer selection is not filtering data from clientMaster. The reason for it to not filter is as ClientMaster and ARInvoices have a single directional relationship. Changing it to both resolves your issue.
8 Replies
- parry2k
Super User
gauravnarchal because your year slicer is on AR table and client count is coming from client master which is not getting filtered by AR table.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
gauravnarchal add a measure for count which will force the filter from AR to Client
Profile Count = CALCULATE ( COUNTROWS ( ClientMaster ), CROSSFILTER ( ClientMaster[ProfileNumber], ARInvoices[ProfileNumber], Both ) )or add a measure to get distinctcount of profile from AR table
Profile Count = DISTINCTCOUNT ( ARInvoices[ProfileNumber] )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
gauravnarchalHi parry2k - Still the count is not correct even after following your suggestion of creating a measure. I have attached the PBIX file for you to have a look at.
Here is the PBIX file. Click Here
- gauravnarchal
Hi parry2k When I'm using the below measure, I am getting the below warning
CROSSFILTER function can only use the two column references participating in a relationship.
Profile Count = CALCULATE ( COUNTROWS ( ClientMaster ), CROSSFILTER ( ClientMaster[ProfileNumber], ARInvoices[ProfileNumber], Both ) )
- parry2k
gauravnarchal not sure why see attached it works fine as suggested earlier
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
You are doing absolutely right using the slicer and card for the count. It should give 1 and rather shoving two.
PFA the updated file. It doesn't use a new measure.
Since the data has only one record in table ARInvoices for 2015 you see the metrix shows 1.
Now the KPI shows 2 because it has two records in ClientMaster.If there were 100 records in ClientMaster it would show 100.
Why is this happening because 2015 slicer selection is not filtering data from clientMaster. The reason for it to not filter is as ClientMaster and ARInvoices have a single directional relationship. Changing it to both resolves your issue.- parry2k
Anonymous great you provided the detail but changing cross filter direction to both is not a recommended/best practice, this can lead to performance issues and other unexpected results. I always want to think thru if this model is large then how it will perform or if there is a complex data model. This is the last thing you want to do. Just my 2 cents.
- parry2k
gauravnarchal do these two tables have the relationship on profile number?
