Calculate count of column values where ( count (another_column) = 2)

[taher]

Hi all,

I have this sample table:

 I want to calculate the count of users, which have two different stores . I use direct query, so i can not use summerize or groub by.
I've tried this code but it needs a shot, which i can not find.

Count of users with two stores = CALCULATE(DISTINCTCOUNT('table'[UserId]);FILTER(ALLSELECTED(table'[UserId]);DISTINCTCOUNT('table'[Store])=2))

Could someone help me please 🙂
Taher

[Zubair_Muhammad · Accepted solution]

@taher

Try this MEASURE

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[UserID],
            "Count", DISTINCTCOUNT ( 'Table'[StoreID] )
        ),
        [Count] = 2
    )
)

[Zubair_Muhammad · Accepted solution]

@taher

Try this MEASURE

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[UserID],
            "Count", DISTINCTCOUNT ( 'Table'[StoreID] )
        ),
        [Count] = 2
    )
)

[taher]

Hi @Zubair_Muhammad,

thank u very much.  You have really saved me with this measure 🙂
The measure with SUMMERIZE WORKED WELL ( I DONT KNOW HOW WITH DIRECT QUERY !!).

I'll try the second measure as well.
I'm very gratefull, thnx a lot 🙂
Taher

[Zubair_Muhammad · Accepted solution]

@taher

Sorry I didn't read you can't use summarize

Try this

Measure 2 =
COUNTX (
    FILTER (
        ALL ( 'Table'[UserID] ),
        CALCULATE ( DISTINCTCOUNT ( 'Table'[StoreID] ) ) = 2
    ),
    CALCULATE ( DISTINCTCOUNT ( 'Table'[StoreID] ) )
)

[taher]

Hi @Zubair_Muhammad,

the second formula works too!

Thanx,

Taher