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.
Anonymous , if program id to store is one to many join them to create a join program , store, fact.
or Merge program and store
or try
calculate(countx(Transactions,related(store[store_id])))
Hey Amitchandak,
Your first suggestion seems to suggest creating a join between 2 dimensions, but my real data model has about 10 dimensions, of which some are 1,5M rows. Don't think that's a good solution right? Or do I misunderstand?
Same for merging, that works nice in my testcase, but not in a proper data model with many dimensions.
The last option seems less performant than my solutions, because it uses an iterator, no? Also, this still relies on transactions, so any stores that didnt have any transactions would not get counted, which is not what I want. I just want a count of the number of stores in the store table for this particular program.
Thanks for thinking along though!
Jaap
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/38490Best Regards,
Yingjie LiIf 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.