topic Re: Count blank in matrix in Desktop
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868397#M416465
<P><LI-USER uid="14019"></LI-USER> ,</P><P>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.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PBI2.png" style="width: 607px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/215699iC70BA4778AC6A260/image-size/large?v=1.0&px=999" title="PBI2.png" alt="PBI2.png" /></span></P><P> </P><P>Then all you have to do is use the matrix visualization. Rows = Associate, Columns = Date, Values = Count of consumers. </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="BI Forum.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/215698i99A6C33195C4C72F/image-size/large?v=1.0&px=999" title="BI Forum.png" alt="BI Forum.png" /></span></P>Sat, 07 Dec 2019 12:27:23 GMTrsimonsen2019-12-07T12:27:23ZCount blank in matrix
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868390#M416462
<P>Hi,<BR /><BR />I have columns in my table as mentioned below.<BR /><BR /></P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Associate</TD><TD>consumers</TD></TR><TR><TD>01-09-2019</TD><TD>A</TD><TD>120104000020</TD></TR><TR><TD>01-09-2019</TD><TD>A</TD><TD>120104000038</TD></TR><TR><TD>01-09-2019</TD><TD>A</TD><TD>120104000046</TD></TR><TR><TD>01-09-2019</TD><TD>D</TD><TD>120104000062</TD></TR><TR><TD>01-09-2019</TD><TD>E</TD><TD>120104000071</TD></TR><TR><TD>01-09-2019</TD><TD>F</TD><TD>120104000089</TD></TR><TR><TD>02-09-2019</TD><TD>A</TD><TD>120104000097</TD></TR><TR><TD>02-09-2019</TD><TD>B</TD><TD>120104000101</TD></TR><TR><TD>02-09-2019</TD><TD>C</TD><TD>120104000119</TD></TR><TR><TD>03-09-2019</TD><TD>A</TD><TD>120104000135</TD></TR><TR><TD>03-09-2019</TD><TD>B</TD><TD>120104000151</TD></TR><TR><TD>03-09-2019</TD><TD>D</TD><TD>120104000160</TD></TR><TR><TD>03-09-2019</TD><TD>E</TD><TD>120104000186</TD></TR><TR><TD>03-09-2019</TD><TD>F</TD><TD>120104000208</TD></TR><TR><TD>03-09-2019</TD><TD>G</TD><TD>120104000216</TD></TR><TR><TD>15-09-2019</TD><TD>A</TD><TD>120104000241</TD></TR><TR><TD>15-09-2019</TD><TD>B</TD><TD>120104000259</TD></TR><TR><TD>15-09-2019</TD><TD>C</TD><TD>120104000283</TD></TR><TR><TD>15-09-2019</TD><TD>E</TD><TD>120104000305</TD></TR><TR><TD>15-09-2019</TD><TD>F</TD><TD>120104000313</TD></TR><TR><TD>15-09-2019</TD><TD>G</TD><TD>120104000330</TD></TR></TBODY></TABLE><P><BR /><BR />I need to create a matrix in the below format.<BR /><BR /></P><TABLE border="1"><TBODY><TR><TD> </TD><TD>01-09-2019</TD><TD>02-09-2019</TD><TD>03-09-2019</TD><TD>04-09-2019</TD><TD>05-09-2019</TD><TD>06-09-2019</TD><TD>07-09-2019</TD><TD>08-09-2019</TD><TD>09-09-2019</TD><TD>10-09-2019</TD><TD>11-09-2019</TD><TD>12-09-2019</TD><TD>13-09-2019</TD><TD>14-09-2019</TD><TD>15-09-2019</TD><TD>16-09-2019</TD><TD>17-09-2019</TD><TD>WORKING DAYS</TD><TD>TOTAL DAYS</TD></TR><TR><TD>A</TD><TD>3</TD><TD>1</TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>4</TD><TD>17</TD></TR><TR><TD>B</TD><TD> </TD><TD>1</TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>3</TD><TD>17</TD></TR><TR><TD>C</TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>2</TD><TD>17</TD></TR><TR><TD>D</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>3</TD><TD>17</TD></TR><TR><TD>E</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>3</TD><TD>17</TD></TR><TR><TD>F</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>3</TD><TD>17</TD></TR><TR><TD>G</TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>1</TD><TD> </TD><TD> </TD><TD>2</TD><TD>17</TD></TR></TBODY></TABLE><P> </P><P>Please help me out for creating measure for calculating no. of days when associate didn't work and no. of days when associate worked.</P><P> </P>Sat, 07 Dec 2019 12:01:30 GMThttps://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868390#M416462ask4atish2019-12-07T12:01:30ZRe: Count blank in matrix
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868397#M416465
<P><LI-USER uid="14019"></LI-USER> ,</P><P>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.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="PBI2.png" style="width: 607px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/215699iC70BA4778AC6A260/image-size/large?v=1.0&px=999" title="PBI2.png" alt="PBI2.png" /></span></P><P> </P><P>Then all you have to do is use the matrix visualization. Rows = Associate, Columns = Date, Values = Count of consumers. </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="BI Forum.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/215698i99A6C33195C4C72F/image-size/large?v=1.0&px=999" title="BI Forum.png" alt="BI Forum.png" /></span></P>Sat, 07 Dec 2019 12:27:23 GMThttps://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868397#M416465rsimonsen2019-12-07T12:27:23ZRe: Count blank in matrix
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868970#M416734
<P><LI-USER uid="175951"></LI-USER> <BR /><BR />I also want to count the dates when associate didn't attend any consumers or the dates for which associte didn't work.<BR /><BR />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.</P>Mon, 09 Dec 2019 04:55:45 GMThttps://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/868970#M416734ask4atish2019-12-09T04:55:45ZRe: Count blank in matrix
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/869112#M416806
<P>hi <LI-USER uid="14019"></LI-USER> </P>
<P>For your case, <STRONG>you must have a dim date table</STRONG> and create the relationship with fact table by date column.</P>
<P>then use [Date] from this dim table in [Columns] of matrix visual.</P>
<P>Here is detail steps, you could refer to:</P>
<P>Step1:</P>
<P>Use <SPAN style="color: #000000;">CALENDAR Function to create a dim date table, eg.</SPAN></P>
<LI-CODE lang="markup">Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))</LI-CODE>
<P>Step2:</P>
<P>Create the relationship with fact data table.</P>
<P>Step3:</P>
<P>Create two measure that</P>
<LI-CODE lang="markup">WORKING DAYS = IF(ISFILTERED('Date'[Date]),COUNTA('Table'[consumers]),DISTINCTCOUNT('Table'[Date]))
TOTAL DAYS = CALCULATE(COUNTA('Date'[Date]))</LI-CODE>
<P> </P>
<P>and here is sample pbix file, please try it.</P>
<P> </P>
<P>Regards,</P>
<P>Lin</P>Mon, 09 Dec 2019 07:13:02 GMThttps://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/869112#M416806v-lili6-msft2019-12-09T07:13:02ZRe: Count blank in matrix
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/870710#M417644
<P><LI-USER uid="74467"></LI-USER> </P><P> </P><P>Your solution is near to my expected results. However, there is a challenge which i am facing:<BR /><BR />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.<BR /><BR />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.</P><P> </P><P>Regards,</P><P>Atish </P>Tue, 10 Dec 2019 11:46:40 GMThttps://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/870710#M417644ask4atish2019-12-10T11:46:40ZRe: Count blank in matrix
https://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/871414#M417951
<P>hi <LI-USER uid="14019"></LI-USER> </P>
<P>It couldn't be achieved that only <STRONG>add a total</STRONG> in the matrix. so they will show in <SPAN style="display: inline !important; float: none; background-color: #ffffff; color: #000000; font-family: 'SegoeUI','Lato','Helvetica Neue',Helvetica,Arial,sans-serif; font-size: 15px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">every associate</SPAN> too.</P>
<P>I recommed put them in two matrix visual <FONT style="background-color: #ffffff;">separately, then show them as below:</FONT></P>
<P><FONT style="background-color: #ffffff;"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="4.JPG" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/216551i838A61452E7BC38E/image-size/large?v=1.0&px=999" title="4.JPG" alt="4.JPG" /></span></FONT></P>
<P> </P>
<P><FONT style="background-color: #ffffff;">Regards,</FONT></P>
<P><FONT style="background-color: #ffffff;">Lin</FONT></P>Wed, 11 Dec 2019 01:58:52 GMThttps://community.powerbi.com/t5/Desktop/Count-blank-in-matrix/m-p/871414#M417951v-lili6-msft2019-12-11T01:58:52Z