I've got a confusing issue with my organisation's new set-up of Snowflake to Power BI.
Some context on the set-up that my colleague has recently carried out:
So that is the context of our set-up. The problem I've had ever since then (in both Desktop and via app.powerbi.com) is that reports are not functioning as I'd expect. For example, in the below screenshot I am querying one of those published tables and the grand total income shows as I expect:
When I drag PaymentCategory onto Rows, the grand total remains the same which is good:
But when I drag CloseDate onto Rows instead, the grand total goes down a lot:
To be clear, I've not applied any filters in this step, I have only replaced PaymentCategory with CloseDate. The same thing happens with some other date fields but also other types of data, so it's not solely linked to date fields as far as I can tell.
The part that really confuses me is that when I look at Snowflake's query history, I can see that the query produced by the CloseDate example above gives me the correct grand total:
sum("AmountIncludingGiftAid") as "C1"
group by "CloseDate"
) as "ITBL"
where not "C1" is null
LIMIT 1000001 OFFSET 0;
As in, when I run this query separately in Snowflake, the grand total for AmountIncludingGiftAid is exactly what I expect. So it appears that some rows are somehow being lost/filtered out once the data gets into Power BI to be visualised.
I hope that's enough detail and makes sense. If anyone has any ideas I'd be very grateful!
Hi @stuartcoggins ,
Without knowing your model is difficult to pin point the error, since you are using dates on the visualization that is incorrect,.
Remember that context in Power BI and DAX are given by a large number of things not only filters.
To give you an example I add a case of a value that would also did not appear when applying a date but on the categories it was correct, when I looked at the model there was a bi-directional filter on a table that was related with the calendar that filtered out my calendar and my totals.
Not saying this is the case, but if you have the totals correct when there is split and when you do a calculation with a category or a date those change this is for sure related with the way the model is setup.
Thank you for your reply. I can appreciate that it's difficult to solve this without being able to see the model set-up! The date column does come from the same table, so I assume that it can't be related to model set-up.
I checked the 'Show items without data' and it didn't appear to make a difference. And I checked the source data in our data warehouse and there don't appear to be any null values.
So thank you for you help with this but I appreciate it might be difficult to do anything further on this!
As refered without any additional information it's rely hard, context transiction may be happening that it may not realize on the images you provide.
If you want to send some more details on this you can send me a PM and we can try and check this.