Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Star Schema problem

Hi all!   I'm struggling a bit with a data model that we are using at work. I'm trying to implement a nice star schema, but I'm running into some restrictions.    It is important to know that all...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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 Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey 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=sharing

 

The 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]))
v-yingjl's avatar
v-yingjl
Community Support
6 years ago

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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    OK thanks,

     

    I was just afraid that there was a better way of doing this. It must be quite rare to have multiple clients with their own transactions etc in a database together 🙂