DAX - How to distinct count two columns with multiple column filters

1
votes

I'm trying to do a distinct count on two columns (PatronID & GamingDay) with a filter on four columns all contained in the same table (dal vPlayByDay). As you can see I tried using the COUNTROWS with FILTER on a GROUPBY however, it doesn't let me include three of the columns I want to filter (SlotTheo, SlotActual & SlotCoinIn) without including them in the GROUPBY. Any help is much appreciated!

For performance purposes, I'm not wanting to add a column to the table that concatenates PatronID and GamingDay. Already tried it and bad performance.

Trips2 = 
COUNTROWS(
    FILTER(
        GROUPBY(
            'dal vPlayByDay', 'dal vPlayByDay'[PatronID], 'dal vPlayByDay'[GamingDay]
        ),
        'dal vPlayByDay'[PatronID] <> 0
        && (
            'dal vPlayByDay'[SlotTheo]' > 0
            || 'dal vPlayByDay'[SlotActual]' <> 0
            || 'dal vPlayByDay'[SlotCoinIn]' > 0
        )
    )
)

The syntax error starts at "[SlotTheo]" since it isn't in the GROUPBY. Including it would hose up the count.

1
powerbidax

1 Answers

0
votes

I would try filtering your table before grouping instead of after:

Trips2 =
COUNTROWS (
    GROUPBY (
        FILTER (
            'dal vPlayByDay',
            'dal vPlayByDay'[PatronID] <> 0
                && ( 'dal vPlayByDay'[SlotTheo] > 0
                || 'dal vPlayByDay'[SlotActual] <> 0
                || 'dal vPlayByDay'[SlotCoinIn] > 0 )
        ),
        'dal vPlayByDay'[PatronID],
        'dal vPlayByDay'[GamingDay]
    )
)