cancel
Showing results for
Did you mean:
Highlighted

## Count blank in matrix

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Count blank in matrix

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.
5 REPLIES 5
Frequent Visitor

## Re: Count blank in matrix

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.

Highlighted

## 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

## Re: Count blank in matrix

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

## Re: Count blank in matrix

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

## Re: Count blank in matrix

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

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.

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors