PowerPivot - Granularity issues with a many-to-many relationship

Gabe B

New Member
Joined
Sep 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have two fact tables, one that is a list of stays at multiple hotels with 4 columns (Location, arrival date, departure date, member ID), another that's a series of transactions with a bunch of details (location, transaction date, member id, item sold, item category, etc). Both tables have the same location, member id, and dates used multiple times, the transaction table has the same item and item category multiple times. I have the appropriate dimension tables for members, locations, items and categories, and a calendar table.

My objective is to evaluate the amount of sales per night stayed at each hotel. I have written the following calculated column using DAX:

Code:
=
CALCULATE (
    SUM ( FactClubChits[Net Amount] ),
    DATESBETWEEN (
        'FactClubChits'[Date of Record],
        'StayWindows'[arrDateDataTextBox],
        'StayWindows'[depDateDataTextBox]),
    FILTER (
        'DimMembers',
        'DimMembers'[ClubMemberCode] = 'StayWindows'[MemberCode]
    ),
    FILTER (
        'DimCompanies',
        'DimCompanies'[CompanyName] = 'StayWindows'[HotelName]
    )
)

This gives accurate results by location, and Member ID but when I create a pivot table for item category I get repeating values for all rows. Is there a way I can alter my dax to promote the granularity that's desired? Thanks in advance for any help and advice someone can provide.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top