Count blank in matrix

[ask4atish]

Hi,

I have columns in my table as mentioned below.

Date	Associate	consumers
01-09-2019	A	120104000020
01-09-2019	A	120104000038
01-09-2019	A	120104000046
01-09-2019	D	120104000062
01-09-2019	E	120104000071
01-09-2019	F	120104000089
02-09-2019	A	120104000097
02-09-2019	B	120104000101
02-09-2019	C	120104000119
03-09-2019	A	120104000135
03-09-2019	B	120104000151
03-09-2019	D	120104000160
03-09-2019	E	120104000186
03-09-2019	F	120104000208
03-09-2019	G	120104000216
15-09-2019	A	120104000241
15-09-2019	B	120104000259
15-09-2019	C	120104000283
15-09-2019	E	120104000305
15-09-2019	F	120104000313
15-09-2019	G	120104000330

I need to create a matrix in the below format.

	01-09-2019	02-09-2019	03-09-2019	04-09-2019	05-09-2019	06-09-2019	07-09-2019	08-09-2019	09-09-2019	10-09-2019	11-09-2019	12-09-2019	13-09-2019	14-09-2019	15-09-2019	16-09-2019	17-09-2019	WORKING DAYS	TOTAL DAYS
A	3	1	1												1			4	17
B		1	1												1			3	17
C		1													1			2	17
D	1		1												1			3	17
E	1		1												1			3	17
F	1		1												1			3	17
G			1												1			2	17

Please help me out for creating measure for calculating no. of days when associate didn't work and no. of days when associate worked.

[rsimonsen]

@ask4atish ,

The first step is to make sure that your consumer's column is data type text. This will allow Power BI to count the distinct numbers.

Then all you have to do is use the matrix visualization. Rows = Associate, Columns = Date, Values = Count of consumers. 

[ask4atish]

@rsimonsen 

I also want to count the dates when associate didn't attend any consumers or the dates for which associte didn't work.

End result should be two columns at the end. 1 should be for total number of days and 2nd for the days when associate worked. Currently the solution provided by you is calculating/showing the days on which associate attend consumers.

[v-lili6-msft · Accepted solution]

hi @ask4atish 

For your case, you must have a dim date table and create the relationship with fact table by date column.

then use [Date] from this dim table in [Columns] of matrix visual.

Here is detail steps, you could refer to:

Step1:

Use CALENDAR Function to create a dim date table, eg.

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Step2:

Create the relationship with fact data table.

Step3:

Create two measure that

WORKING DAYS = IF(ISFILTERED('Date'[Date]),COUNTA('Table'[consumers]),DISTINCTCOUNT('Table'[Date]))

TOTAL DAYS = CALCULATE(COUNTA('Date'[Date]))

and here is sample pbix file, please try it.

Regards,

Lin

[ask4atish]

@v-lili6-msft 

Your solution is near to my expected results. However, there is a challenge which i am facing:

The powerbi file which you shared is showing two measures for every associate. Working Days and Total Days. Here for every date, its showing total days as "1" and for working days its showing count of cosumers he handled.

Now, I do not want "Total Days" column to be shown of every associate and want this measure as a column at last in the matrix. So that I can see how many days associate wasn't available.

Regards,

Atish 

[v-lili6-msft]

hi @ask4atish 

It couldn't be achieved that only add a total in the matrix. so they will show in every associate too.

I recommed put them in two matrix visual separately, then show them as below:

Regards,

Lin