cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AvPowerBI
Helper V
Helper V

Value Duplicating on Column - Matrix Chart

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

 

4 REPLIES 4
v-deddai1-msft
Community Support
Community Support

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

Hi Dedmon

 

I have saved it to onedrive on the following link:

 

 
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

Hi,

 

Anyone out there that has had this problem before?

 

Thanks

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?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors