Struggling with displaying a measure in a grouped table

[AdamShaw]

Hi All,

I have the below measure (might not be the easiest way to achieve what i want):

CALCULATE(COUNT('client_status'[clientid]),

FILTER('client_status', 'client_status'[statusid] IN ({23,24,25,26})),

FILTER('client_status',[active]=TRUE),

FILTER('client_status',[clientid] in DISTINCT('Advisor Clients'[clientid])),

FILTER('client_status',[clientid] in DISTINCT('client'[id])),

FILTER('client',[deleted]=false),

FILTER('client_status', 'client_status'[userid] in DISTINCT('user'[id])))

This gives me the correct volume when I cross check against my database for a total. 
However I want to display this in a table as below:

User	Volume
A	10
B	1
C	6
D	9

Where user is the id in the 'User' table referenced. 
However the table just shows blank.
If I change the query to a specific user id it filters correctly. What am I missing to allow me to group the outcome by user?

[v-zhangti · Accepted solution]

Hi, @AdamShaw 

You can try the following methods.

Gather all the columns needed for Client Table and Advisor Table into Client Status Table.

Deleted2 = LOOKUPVALUE(Client[deleted],Client[id],[clientid])

Clientid2 = LOOKUPVALUE(Advisor[clientid],Advisor[clientid],[clientid])

Measure =
CALCULATE (
    COUNT ( 'Client Status'[clientid] ),
    FILTER (
        ALL ( 'Client Status' ),
        [statusid]
            IN ( { 23, 24, 25, 26 } )
                && [active] = TRUE ()
                && [Clientid2] <> BLANK ()
                && [Deleted2] = FALSE ()
                && [user] = MAX ( 'Client Status'[user] )
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@AdamShaw , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please try a measure like

CALCULATE(COUNT('client_status'[clientid]),
FILTER('client_status', 'client_status'[statusid] IN ({23,24,25,26}) && 'client_status',[active]=TRUE() && 'client_status',[clientid] in DISTINCT('Advisor Clients'[clientid]) &&
'client_status',[clientid] in DISTINCT('client'[id])),
FILTER('client',[deleted]=false()))

[AdamShaw]

@amitchandak Tried your new measure but I get the same result. The total from the measure is fine. It just doesnt show when displayed in a table. 

Example data: 
Client Status Table:

clientid	statusid	active	user
1	23	TRUE	a
2	34	TRUE	b
3	46	TRUE	c
4	25	TRUE	b
1	97	FALSE	c
6	1	TRUE	a
7	24	TRUE	b
3	28	FALSE	a
9	26	TRUE	c

Client Table:

id	deleted
1	FALSE
2	FALSE
3	FALSE
4	TRUE
5	FALSE
6	FALSE
7	FALSE
8	FALSE
9	FALSE

Advisor Table:

clientid

1

3

4

5

6

9

There are other columns in these tables, but not relevant to the query I am trying to write. 
I want to get a count, by user where the statusid matches my list, the client is not deleted in the client table and exists in the advisor table. In this case I would be expecting:

user
a	1
b	0
c	1