Problem on trying to show 0 when BLANK

[RenanPNog]

Hello everyone!

So, i'm having a tough time on trying to display 0 when my measure results in BLANK.

I'm also attatching the powerBI file here so you guys can fully comprehend what i'm seeking... (** update, tried guys, but couldn't share file)

PS: sorry to use "id's", but it was the only way to keep info confidential, that's why i used product_id 3 on examples, to make it easier.

I have soma dim tables and there is a relationship between them, i'm also applying filters directly on my page (filter right side screen settings).

For example: My product_id = 3 has these campaigns (241 and 242), and each campaign has some course_id's (195, 209, 310, 504), bellow i'll use the campaign 241 example.

My issue is: when i try to insert a specific measure doing calculations on my fact table, sometimes there is no records (in fact table) to these course_ids, which would result in BLANK. But i really need to show 0 value, even when this record doesn't exists in my table. For example, i'm gonna create a measure where it shows me sales for the last 7 days.

When i place this measure in my table, it hides me some course_id's, which is totally fine, as there is no record for those guys.

BUT, when i try to force a 0 value, to just make it better to visualize and to show to my client that this course_id is not missing, that's my result:
1st - i took off comments from previous measure "calculation_ Last Week Registrations", so i have my coalesce function, on when there's no value, shows me "0"

RESULT:

IT IGNORES MY PRODUCT FILTERING!!!
I did try IF condition, +0 condition, and everything else.... but nothing worked, my turnaround at the moment is to place a course_id filter on pannel and manually filter courses for this product. But guys, this turnarond is giving me goosebumps since in the future i'm getting multiples differente course_ids.

EXEPCTED RESULT:
241 - 151 - 0
241 - 191 - 0
241 - 195 - 4
241 - 209 - 2

Am i missing some basic info really bad? Can any good soul help me?

Thank you all in advance!

[ajohnso2]

Can you provide a sample model, if your having trouble you can upload it to onedrive and share the link here

[RenanPNog]

Sure, @ajohnso2 !

Here it is: Test.pbix

Can you chek if it's working, and message me?

[ajohnso2]

I looked at your model and didnt change anything in your calc.

This is what you were intending? I did actually change the aggregation for your ID columns - you may want to take care of that, change from Sum to Dont summarise for all ID's (pretty sure this is the issue with your product filtering. You are filtering the sum of product id = 3 rather than the distinct values of product id = 3)

Result when fixed aggregation:

[ajohnso2]

It looks like you changed your aggregations on the visual only??

See below:

Please ensure you spend more time checking your model is correctly 'modelled' before moving onto reporting. I.e setting/checking datatypes, integer ID's should never be set as sum. 

Andy

[RenanPNog]

Hi, Andy!

That's weird.... because the new link is supposed to have this corrections. Check it out:

What am i missing?

[ajohnso2]

Please move your page level filter to filters on the visual as per below.

[RenanPNog]

Any news? As it looks on my last screenshot, i moved the filters, but still not working.

I found some videos on youtube but they are not really good, it seems that forcing to appear zero results in this error always and i would probably need a big and complex turnaround to fix this

[RenanPNog]

Follows:

[RenanPNog]

Hi @ajohnso2, thanks for the reply!

Unfortunatelly i couldn't replicate with the info that you sent me. Got the file, changed the aggregation columns, inserted the table, removed columns summarizations in the table, inserted measure.... and still got other course_id's 😞

Here's my result:

I'll share a new link bellow, the modifications i did based on your recomendations (removing aggregations)

Test.pbix
PS: my tests are at the "Verific Values" tab

[lbendlin]

Please explain why you want to do that in the first place.  It is considered bad practice.

[RenanPNog]

@lbendlin

The reason why i want to do that is because in my final report visualization i have many product_id's and campaigns, and i'm going to need to filter both of them in different ways, each product_id and campaign_id has different and multiple course_id's, and i would not like to filter my courses manually, since there are lots of'em.

I tried to explain this here "But guys, this turnarond is giving me goosebumps since in the future i'm getting multiples differente course_ids". I'm sorry if it was not clear enough, didn't mean to make it hard for you guys.

Thank you