Forum Discussion
Star Schema problem
Hi Anonymous ，
You are right. Currently userelationship is the better way to solve this issue because your relationships between the store table and transaction table is [store id] instead of [programm id]. In this case, the result of VN06 must be 3 rather than 5 unless you choose userelationship to pass [programm] parameter.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
You can consider sharing a simple dummy sample file with the star schema like your picture shows for further discussion.
Sample file and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Fair enough, here is the barebones example with the expected result:
https://drive.google.com/file/d/183cMwc8M3xgpXxYaGK_qGA31u_MaZEwa/view?usp=sharing
Just be mindful that my real model has 10+ dimensions with a few having millions of rows.
Thanks!
Hi Anonymous ,
Try to change to cross filter direction from single to both
By the way, in your table, there is only three rows about VN06 so the value of VN06 should be 3, how could it be 6...
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
For this scenario Crossfilter is your friend.
Try a measure like this :-Counting_Store_Crossfilter =CALCULATE(COUNTROWS(Store),CROSSFILTER(Transactions[StoreID],Store[StoreID],Both))
Note:- No need to change the direction of the relationships to bothThe result which you want can be achieved with this. You can further explore more on crossfilter. There are a lot of you tue videos too. Hope this helps
Regards,HemantHey both:
"By the way, in your table, there is only three rows about VN06 so the value of VN06 should be 3, how could it be 6..." Arg you are right, that was a typo. Sorry!
Thanks a lot for your suggestions! However, both suggested solutions go via the transactions table, and both rely on us having transactions in the transaction table for each storeID that we want to count. I want to count all the stores regardless of whether transactions exist for those stores.
I created a new test file that shows this problem a little better. There are 2 stores in VN06 that have 0 transactions. The total number of stores should be 5.
https://drive.google.com/file/d/183cMwc8M3xgpXxYaGK_qGA31u_MaZEwa/view?usp=sharingThe best solution I have come up with so far is an inactive relation between Store and Program, and then this measure:
Counting_Store_UseRelationship =CALCULATE(COUNTROWS(Store),USERELATIONSHIP(ProgramID[Program ID], Store[ProgramID]))Hi Anonymous ，
You are right. Currently userelationship is the better way to solve this issue because your relationships between the store table and transaction table is [store id] instead of [programm id]. In this case, the result of VN06 must be 3 rather than 5 unless you choose userelationship to pass [programm] parameter.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.