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
Hi MFelix,
Thanks for weighing in,
The blank values are correct in the way the query is currently written. A change to the view to be left outer on a few tables will resolve that issue.
This although is unrelated to my current issue. When filtering on the value A00010140 I receive an empty report, but the data clearly shows as available in the data view.
In the case of how this data is structured, I have merged my queries into one. The functionality of data sets is rather limiting and would not allow for a star schema due to unavoidable 1 to many situations in the data.
Hi Anonymous,
Can you share the PBIX file so that I can check it out, if you want due to sensitivity of data you can do it through private message.
Regards,
MFelix
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.