Value Duplicating on Column - Matrix Chart

[AvPowerBI]

Hi,

I have 7 tables in my data model, which consist of the following tables:

PersonCalendar

Has a field called PersonId which is populated  per day for the last 13 months, includes flag fields to show if person should have been working, on holiday, sick etc.. 

This joins to the table FactVisits by PersonId and Date i.e 300_20200610 (300 is PersonId, _ underscore to seperate the two fields and 20200610 being the Date field in DDMMYYYY format)

FactVisits

Transaction table that will show visits by a PersonId, other fields would be like VisitId, Visit From (DateTime DataType), Visit To (DateTime DataType), Location etc..

This joins to the table PersonCalendar by PersonId and Visit From (I would convert this field to a Date format) i.e 300_20200610 (300 is PersonId, _ underscore to seperate the two fields and 20200610 being the Date field in DDMMYYYY format)

DimCustomer

Dimension Table to Customer Name, this joins to FactVisits

DimUser

Dimension Table that holds information on Persons Details, this joins to FactVisits

DimPlaceA

Dimension Table to show Place Area A for a Person, this joins to PersonCalendar

DimPlaceB

Dimension Table to show Place Area B for a Person, this joins to PersonCalendar

DimPlaceC

Dimension Table to show Place Area C for a Person, this joins to PersonCalendar

I have created a Matrix table and have two fields in Rows (One field from the table DimCustomer and one field from the table DimUser) and one field in the Values, for some reason though the Values are repeating for each Row, the field in Values is called Available and is a simple DAX formula of :

Available = SUM('PersonCalendar'[AvailableDayFlag])

Any reason why this is happening, I have read online about possibly using the DAX function SUMX or do an IF Statement including the function HASONEVALUE etc... but I want to make sure the measures do not go AWOL by drilling down to the next Dimension.

Any ideas?

Thanks

[v-deddai1-msft]

Hi @AvPowerBI ,

I can't write down the exact formula for your reference based on the information you gave me. Would you please show us some sample data and expected output or pbix file by onedrive for business?

I suggest you use ISINSCOPE in your formula, please refer to the similar post:  https://community.powerbi.com/t5/Desktop/Participation-inside-a-Hierarchy/td-p/1106616

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[AvPowerBI]

Hi Dedmon

I have saved it to onedrive on the following link:

https://1drv.ms/u/s!Aknl2UdxdHn0bki7BwPJcqZuvZY?e=kPQQ9d

You will have to download the files (xlsx & pbix) to your C Drive in a folder called PowerBI if you needed to refresh the Report.

Thanks

[AvPowerBI]

Hi,

Anyone out there that has had this problem before?

Thanks

[AvPowerBI]

Hi Dedmon, Thanks for help so far, how do I attached the pbix to show you the issue? Or is there an alternative for you to be able to download the pbix file?