Using ALL() with SUMMARIZE() and ADDCOLUMNS()

[tachydidaxy]

I'm trying to get a count of customers who have visited a particular outlet on our property. I have a table that has entry events in which the member_id of the visiting member is logged along with the date and time of entry. 

In our situation we have a primary member and secondary/dependent members. The primary member is the primary member on the account, the secondary members are dependent members for the primary member. If a secondary member visits our outlet the charges are billed to the primary member.

I need to get accurate counts of the total visits, and also be able to break down according to primary member vs secondary member visits as needed.

I've constructed a matrix visual that shows all the currently active primary members; I'd also like to include columns that will count the "primary member visits" and the "secondary member visits" to this same matrix.

I've tried by using summarize and addcolumns as shown below:

Fitness Check-Ins = 
VAR currMember =
    IF ( HASONEVALUE( 'Member'[member_id] ), VALUES ( 'Member'[member_id] ) ) 
RETURN 
    SUMX ( 
        ADDCOLUMNS(
            SUMMARIZE (
                'FitnessCheckIns',
                'Date'[Date],
                'FitnessCheckIns'[member_id],
                'FitnessCheckIns'[bill_to_member_id]
                 )
            , "mycount", CALCULATE( COUNTROWS ( 'FitnessCheckIns' ) ) ),
        [mycount]
    )

The problem is that works well and gives me the count I need, but is only returning the visits for the primary member and not all the secondary members. I'm not sure how I'd use ALL() in order to expand the context to include all the secondary members (i.e. all members where 'Member'[bill_to_member_id] = 'Member'[member_id] of the current row context.

I've tried the following with no luck

Fitness Check-Ins (Includes Secondary Members) =
VAR currMember =
    IF ( HASONEVALUE ( 'Member'[member_id] ), VALUES ( 'Member'[member_id] ) )
RETURN
    SUMX (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    'FitnessCheckIns',
                    'Date'[Date],
                    'FitnessCheckIns'[member_id],
                    'FitnessCheckIns'[bill_to_member_id]
                ),
                "mycount", CALCULATE ( COUNTROWS ( 'FitnessCheckIns' ) )
            ),
            'FitnessCheckIns'[bill_to_member_id] = currMember
        ),
        [mycount]
    )

Here's the goal of the finished visual where the first column in the member_id, the second column is my measure counting the currently active primary members. (here Fitness Check-Ins2 column = primary member visits measure, and Fitness Check-Ins3 = desired total visists including secondary members measure )

And here's the relevant portion of this rather small data model:

Any assistance is appreciated.

[tachydidaxy · Accepted solution]

So I was able to get something cobbled together that works for my current needs.

First, I had to create another (inactive) relationship between the Member table and the FitnessCheckIns table ( 'Member'[member_id] 1 --> * 'FitnessCheckIns'[bill_to_member_id] ).

I also (?had to?) create(d) a calculated column in the FitnessCheckIns table that showed if the current check-in event was for a spouse member etc.

dependent_type = LOOKUPVALUE( 'Member'[dependent_type], 'Member'[member_id], 'FitnessCheckIns'[member_id] )

I was then able to use the following:

Fitness Check-Ins Spouses = 
VAR currMember =
    IF ( HASONEVALUE ( 'Member'[member_id] ), VALUES ( 'Member'[member_id] ) )
RETURN
    CALCULATE (
        SUMX (
            ADDCOLUMNS (
                SUMMARIZE (
                    FILTER (
                        'FitnessCheckIns',
                        'FitnessCheckIns'[dependent_type] IN { "Spouse", "Significant Other" }
                    ),
                    'Date'[Date],
                    'FitnessCheckIns'[dependent_type],
                    'FitnessCheckIns'[bill_to_member_id],
                    'Member'[member_id]
                ),
                "mycount", CALCULATE ( COUNTROWS ( 'FitnessCheckIns' ) )
            ),
            [mycount]
        ),
        USERELATIONSHIP ( 'Member'[member_id], 'FitnessCheckIns'[bill_to_member_id] )
    )

Not sure if it's the ideal way to accomplish or not, but seems to be working for me.

[v-yuta-msft]

Hi tachydidaxy,

"I need to get accurate counts of the total visits, and also be able to break down according to primary member vs secondary member visits as needed."

<--- What's your expected requirement? Can you give some sample data for test?

Regards,

Jimmy Tao

[tachydidaxy]

---

@v-yuta-msft wrote:

Hi tachydidaxy,

"I need to get accurate counts of the total visits, and also be able to break down according to primary member vs secondary member visits as needed."

<--- What's your expected requirement? Can you give some sample data for test?

Regards,

Jimmy Tao

---

Thanks @v-yuta-msft. I've linked to a sample PBIX.

The main issue is we're looking at anlayzing only a specific "category" of member/customer. I have a measure ('Member'[Active Resident Primary Members]) that counts all the members/customers that are of the desired category.

When I place this measure on the matrix I'm returned only the members we want to study. (See matrix on right hand of page called "Members to Analyze") for reference.

I then need to count all the check-ins to our Fitness facility by these same members and their dependent members. I need specifically the count of the primary member's spouses.

So the final matrix needs to show on the row level the members who are of the 'Member'[Active Resident Primary Members] measure broken out by their type (Social vs Comprehensive).

Everyone that is an Active Resident Primary Member should be included in this matrix, even if they or their dependents haven't checked into the fitness facility (part of what we're trying to figure out is who doesn't use the facility, so we need to keep blanks).

Then for the columns/values it would need the following:

• Active Resident Primary Members measure - this ensures I always see the total list of Active Resident Primary Members we want to analyze (please note I've set the visual level filter so that this measure is never blank effectively ensuring I'll always see these members displayed, even if they haven't visited the fitness center).
• Fitness Check-Ins All - total count of all check-ins to the fitness facility by the primary member and dependent members of the current row's context
• Fitness Check-Ins Primary - total count of all check-ins to the facility by only the primary member of the current row's context
• Fitness Check-Ins Spouses - total count of all check-ins to the facility by only the spouse of the current row's context