cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

Count blank in matrix

Hi,

I have columns in my table as mentioned below.

DateAssociateconsumers
01-09-2019A120104000020
01-09-2019A120104000038
01-09-2019A120104000046
01-09-2019D120104000062
01-09-2019E120104000071
01-09-2019F120104000089
02-09-2019A120104000097
02-09-2019B120104000101
02-09-2019C120104000119
03-09-2019A120104000135
03-09-2019B120104000151
03-09-2019D120104000160
03-09-2019E120104000186
03-09-2019F120104000208
03-09-2019G120104000216
15-09-2019A120104000241
15-09-2019B120104000259
15-09-2019C120104000283
15-09-2019E120104000305
15-09-2019F120104000313
15-09-2019G120104000330



I need to create a matrix in the below format.

 01-09-201902-09-201903-09-201904-09-201905-09-201906-09-201907-09-201908-09-201909-09-201910-09-201911-09-201912-09-201913-09-201914-09-201915-09-201916-09-201917-09-2019WORKING DAYSTOTAL DAYS
A311           1  417
B 11           1  317
C 1            1  217
D1 1           1  317
E1 1           1  317
F1 1           1  317
G  1           1  217

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Count blank in matrix

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

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

View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

Re: Count blank in matrix

@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.

PBI2.png

 

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

BI Forum.png

Highlighted
Advocate III
Advocate III

Re: Count blank in matrix

@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.

Highlighted
Community Support
Community Support

Re: Count blank in matrix

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

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

View solution in original post

Highlighted
Advocate III
Advocate III

Re: Count blank in matrix

@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 

Highlighted
Community Support
Community Support

Re: Count blank in matrix

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:

4.JPG

 

Regards,

Lin

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors