COUNT and IF cause lines to be duplicated, how can I solve this?

[Anonymous]

Hello,

I am suffering with this problem. The count DAX formular with " + 0" causes lines to be duplicated.

First, here is the tables:

I want to count the number of comment that each BookingNo has. 
My formular is

CountCommentWithOutZero = COUNTROWS(Fact_Comment)

This formular returns correctly and causes no duplicated line . 

But I want to fill the blank cells with "0". I have read some where and they said adding a +0 like this to the fomular should work.

CountCommentWithZero = COUNTROWS(Fact_Comment) + 0

This time, blank cell is now filled by 0, but it causes duplicated lines. (See the bottom table in the image below) And I can't understand why this happen. I just want the blank cells to be filled with 0.

Also, I want to write a simple formular that display if this Booking has any comment or not, if the countrow returns any value that is greater than 0, it should return true, else it should return 0.

My formular is:

HaveComment = IF (COUNTROWS(Fact_Comment)> 0, "YES", "NO")

But it again causes duplication. (See the top right table in the image below).

Here is the Power BI file.
https://drive.google.com/open?id=1yXuDHtjmY6rhACznW0PowtYbyFpKPMA3

Can anyone help me solve this problem? And please explain to me why the duplication happens. If the problem is related to the table design then please feel free to criticize it, as these tables are simple prototype of our company data warehouse, so design flaws should be discoverd as soon as possible.

[Anonymous]

Bump.

Please help.

[Anonymous]

Bump.

Also, I notice that, if I use calculated column, this problem can be solve. But I am afraid that calculated columns may put heavy duty on the analysis service each time the tabular model is processed, so I try to avoid using calculated column as much as possible. But the measure just doesn't work here, so I suspect that I might be using measure incorrectly. Can somebody help me?

[Anonymous]

Bump.

Please help.

[amitchandak]

I think Many to Many join between Fact_booking_details and Comments is causing that. 

[Anonymous]

If so, how can I solve this?

I just want to drill acrros these data marts. These datamarts use a same degenerate dimension "B_Access_Key", and I just want to aggregate the number of wanted type of comments of each B_AccessKey, then join with other dim table to get more detailed information of each B_AccessKey. 

I have asked if the action of joining 2 facts table together in Power BI using a degenerate dimension is legit or not on this forum before, and answers seem to be that this action is legit.

Do you have any idea of what should be done in this situation?