DAX - Count number of items

[JoelTib]

Hello all,

I am after some help with a DAX measure.

I have a dataset that resembles the following:  

Date	Client	Assigned To	Week Beginning
13-Aug	A	Bill	7-Aug
13-Aug	B	Bob	7-Aug
13-Aug	C	Ben	7-Aug
13-Aug	D	Barry	7-Aug
13-Aug	E	Becky	7-Aug
13-Aug	F	Bill	7-Aug
13-Aug	G	Bill	7-Aug
13-Aug	H	Bob	7-Aug
13-Aug	I	Ben	7-Aug
6-Aug	A	Bill	31-Jul
6-Aug	B	Bob	31-Jul
6-Aug	C	Ben	31-Jul
6-Aug	D	Barry	31-Jul
6-Aug	E	Becky	31-Jul
6-Aug	F	Carl	31-Jul
6-Aug	G	Bill	31-Jul
6-Aug	H	Bill	31-Jul
6-Aug	I	Bill	31-Jul
30-Jul	A	Bill	24-Jul
30-Jul	B	Bob	24-Jul
30-Jul	C	Ben	24-Jul
30-Jul	D	Stan	24-Jul
30-Jul	E	Matthew	24-Jul
30-Jul	F	Eric	24-Jul
30-Jul	G	Nath	24-Jul
30-Jul	H	Charles	24-Jul
17-Jul	A	Charles	17-Jul
17-Jul	C	Nath	17-Jul
17-Jul	D	Eric	17-Jul
17-Jul	E	Bill	17-Jul
17-Jul	G	Bob	17-Jul

I have been trying to write a DAX measure that will work under two situations. 

Firstly, I would like it to filter the 'Week Beginning' column for rows containing data from the current week (i.e., with respect to TODAY()), or the most recent week in the table, and then give me a count for the number of 'Clients' each 'Assigned to' name has.  

For example, from the data above I would like the following output (i.e., this is filtering for 'Assigned To' names that have the most recent 'Week Beginning' date of the 7th-Aug): 

Assigned To	Client Count
Bill	3
Bob	2
Ben	2
Barry	1
Becky	1

Secondly, if however, I have selected a particular week beginning date from another visual on my Power BI dashboard, I would like the output to show me the list of 'Assigned to' names, and their 'Client Count' for the particular week I have selected on said visual.   

Is this possible?  I am still very much learning the intricacies of DAX so any help would be most appreciated.  

Kind regards,

Joel

[v-rzhou-msft · Accepted solution]

Hi @JoelTib ,

I suggest you to create an unrelated table to help our calculation.

DimWeek Beginning = 
VALUES('Table'[Week Beginning])

Measure:

Count of Client =
VAR _SELECTVALUE =
    SELECTEDVALUE ( 'DimWeek Beginning'[Week Beginning] )
VAR _RECENTWEEKBEGING =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Week Beginning] <= TODAY () ),
        'Table'[Week Beginning]
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Client] ),
        FILTER (
            'Table',
            'Table'[Week Beginning]
                = IF (
                    ISFILTERED ( 'DimWeek Beginning'[Week Beginning] ),
                    _SELECTVALUE,
                    _RECENTWEEKBEGING
                )
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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

[v-rzhou-msft · Accepted solution]

Hi @JoelTib ,

I suggest you to create an unrelated table to help our calculation.

DimWeek Beginning = 
VALUES('Table'[Week Beginning])

Measure:

Count of Client =
VAR _SELECTVALUE =
    SELECTEDVALUE ( 'DimWeek Beginning'[Week Beginning] )
VAR _RECENTWEEKBEGING =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Week Beginning] <= TODAY () ),
        'Table'[Week Beginning]
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Client] ),
        FILTER (
            'Table',
            'Table'[Week Beginning]
                = IF (
                    ISFILTERED ( 'DimWeek Beginning'[Week Beginning] ),
                    _SELECTVALUE,
                    _RECENTWEEKBEGING
                )
        )
    )

Result is as below.

Best Regards,
Rico Zhou

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

[JoelTib · Accepted solution]

Dear Rico,

Thank you so much for your help.  This solution is great.  Does the job perfectly.  Many, many thanks!

Cheers,

Joel

[lbendlin]

DAX is not always necessary. Consider a graphical solution to your question based on a Matrix visual

[JoelTib]

Dear @lbendlin,

Thank you very much for your help.  Much appreciated.

Cheers,

Joel