Forum Discussion
empty report when filtering
Hi Anonymous,
the question is your Open Debt colum, you are using a summarize option (SUM) in this case this is calculating the sum of the values however for the specif code you are refering of Banner ID that column is null in this case this don't sum as 0 since all values as null it doesn't appear on your table.
If you go to your last column in the table and select the don't summarize the A00010140 will appear but with blank / null values.
One question I didn't understood why are you appending merging all you table into a giant one and not making relationship between tables and uisng measure to make your calculations?
Regards,
MFelix
Thanks for that insight. I didn't realize that Power BI would ignore the entire record in such situations. Other tools will either not consider the record in the calculation or assume its 0.
I used the merge option because Power BI won't allow you to create many to many relationships which exists all over my data. This data is transactional in nature and not aggregated. I flatten it wherever possible, but that isn't always the case. Also power BI didn't allow me to utilize data from all 4 datasets in the report. I could only use 2 without an error. When I searched on the error I was receiving, Microsoft documentation indicated this to be expected behavior. If I was dealing with aggregate tables or KPIs, this probably wouldn't be a problem, but all of the data is one to many, which doesn't work as I would expect.
To be honest, I would prefer to create relationships as opposed to merging the data, that way I would be able to reuse more datasets. Unfortunately this does not appear to be a possibility without improvements to Power BI.
Hi Anonymous,
The many to many relationships can be worked with dimension table to make the links between the several tables and using the bidirectional filters.
Not really sure how you hacve your data model but if you need any help please share some insights on it so we can see together how you can do it.
Regards,
MFelix
The reason why I merge the data is because I'm not dealing with a typical sales set of fact and dimensional tables. This is student data which has a bunch of fact tables joined with each other. For example you have in a simplified scenario this below data model:
student table 1:* Curriculum *:* Course 1:* Course Section 1:* Attendance.
In the real world, the Student table has many different many to many relationships snowflaked off of it as well does the curriculum table and course section tables. In the file I shared with you, its a simple many to 1 to many relationship. But when defining that relationship in the tool, it won't allow you to use data from all 3 tables in the same tabular report. I've looked into the create new table functionality that Microsoft talks about and its not avaialable when importing views from Oracle. The only way I've been able to find so far that allows me to work with the data in the way I need to is to use the merge functionality and make it all 1 large table. Unfortunately, this also means that I have to import the data seperately for each report. Something I would prefer to avoid... but I am where I am....
Also, here is a link to a different discussion dealing with the same issue as I am having. Problem agreggating across many to many link table in Power BI
in the comments I found this:
The user is hitting a current limitation in Power BI. You cannot build a single visual having columns from tables with an N:1:N, even if the relationships have bi-directional cross filtering, unless you also have a measure, or aggregate, in the visual. As the user has discovered, the workaround is to include at least one measure, or aggregate, in the visual.
Ideally, we would improve this behavior. However, we have not yet found a behavior that we like that works across arbitrary model structures. Therefore, I cannot provide an estimate for when we might fix this. I suggest the user add a measure, or aggregate, to their visual to workaround this problem. One option is to drag the same column into the visual twice and switch one of the columns to use a Count aggregate.In this case they are talking about a visual, where I have a report. But the result is the same. Furthermore, this posts goes on to point out that even when adding a measure, the joining of the data doesn't happen correctly. In my research, I haven't found any other way to deal with this other then merging data.
I'll pm you a link to a sample data model if you would like to show me what I may have missed, but from what I have been able to find within microsofts files and the knowledgebase, merging tables is the only way to get the result I need.
At the end of this, I might just ignore the table merge all together and just right the custom query, as I don't seem to be getting any value at all from the query modeling capabilities of Power BI. It just isn't robust enough to deal with such a dataset.
Thanks,
Eric