Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have two tables:
T1:
TYPE | KEY | NAME | AMOUNT |
TYPE1 | AAA | A1 | 2 |
TYPE2 | AAA | A1 | 2 |
TYPE1 | BBB | B1 | 3 |
TYPE2 | CCC | C1 | 4 |
TYPE1 | DDD | D1 | 1 |
T2:
KEY | CUSTOMER |
AAA | A2 |
AAA | A3 |
BBB | B2 |
CCC | C2 |
And the column KEY is the relationship key between these two tables; it's a many-to-many relationship.
And when I create a table in my power bi:
Question 1: You can see the AMOUNT of D1 is missing. Why?
Question 2: After I change the column sort like the below image, the AMOUNT is also missing, but after I change the format of AMOUNT to "Don't Summarize", then the AMOUNT show up. Why?
Thanks in advance.
Aiolos Zhao
Proud to be a Super User!
Solved! Go to Solution.
I have given you some advice about data modeling - that advice is correct. I have taken a closer look at your sample data. What you need to understand about Power BI is that the rows in the tables will filter the other tables in the model (in the direction of the filter arrows only).
Back to your questions.
1. The reason the value D1 is missing is due to filtering. When you place Name and Customer onto the rows in the table, you are putting filters on both of those columns. In otherwords, you are saying "Show all the data where Name = D1 and Customer = Blank. There is no such data in your model, and that is why it is not showing. If you remove the Customer from the visual, then you will see the value.
2. When you say "don't summarise", you are changing the behaviour of the model. You are now filtering on all 3 columns. Power BI shows the list of values for all 3 columns.
The test data you have shared is not very descriptive, so it is not clear what it is or how it should be used. Power BI is a semantic data modelling tool. It is designed to accept entitiy based dimension tables and also fact tables (dimensional modelling). I can't advise you on how to set up your data because there is no context in the table names.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |