Forum Discussion
Power bi modeling( Star or snowflake)
can anyone help me to change the model or show me where went wrong to show actual results.
I have below model from my practice data.
data contains total 20 transactions and their product type , brand.
Not all 20 transactions contains agreements and offices.
when i inlcude transactions with instalment (sum of amount+instalment date), branches ... report is showing only non blank branch and agreement transactions..im missing transactions which does not have agremments,branches ,which has instalment entries.
below are two requirements in test cases.
1)
| Transaction id | it should list all available transactions |
| praduct type id | it should list product type of transaction |
| brand name | it should show brand of transaction |
| agreement id | it should show all agreements associated with transaction if no agreement is available it should show blank |
| branch id | it should show all branches associated with transaction(Through agreement) if no branch is available it should show blank |
| instalment amount | sum of instalments amount |
| instalment date | date of instalment |
My output: it showing only non blank branch and agreement transactions with their instalments ,intalment date.
expected output: should list all transactions
error : Im missing some transactions which contains blank agreement and its branches.
2)
| Transaction id | should list all available transactions |
| agreement name | should show respective agreement,if no agreement is available it has to be blank |
| branch id | should show respective branch(through agreement),if no branch is available it has to be blank |
Expected output : it should list all transaction
output : Error in visual
below is pbix ondrive link
https://1drv.ms/u/s!Asxus9sKyugxaZy9vrUQO7uPQCI?e=am8SNq
below is the data
https://1drv.ms/x/s!Asxus9sKyugxanE9I00kIonmE78?e=QUwAer
Anonymous
sorry π are you sure you need transactionId as third column in visual, not the first?
Hi Anonymous
there is solution with Query Editor in the attach
Drop relationships branch-agreement and installment-transaction
Merge Transaction and branch tables
Merge transaction and installment tables
7 Replies
- az38
Community Champion
Anonymous
finally, you have Branch-Transaction many-to-many relationships by agreement_id field
How do you want to solve this amigious? Agreement table won't help you there
az38 ,
This is a kind of challenge to me.
I got a solution by left joining transaction table with agreement (left joining agreement with office), It gives me all list of transactions with their agreements and offices,blank agreement and office data for transaction which does not have agreement and office, This is giving me expected result,,,, This leads my model to have a many to many relationship between transaction and instalment table......Im not satified with my approach (model having many to many relationship).... Searching for right approach...
- az38
Anonymous
ok. question. First of all, it's a business question. We have to understand business needs in this report
you have Installment[Amount] for each transaction
for example
Transaction ID Installment[Amount] 1 10 But each transaction could have a few branches, for example
Transaction ID Branch ID 1 2 1 3 1 4 So, how should be filled field Installment[Amount] for each pair Transaction-Branch in this case?
Transaction ID Branch ID Installment[Amount] 1 2 ? 1 3 ? 1 4 ?