Forum Discussion

ask4atish's avatar
ask4atish
Icon for Advocate III rankAdvocate III
6 years ago
Solved

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.

 

Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

5 Replies

  • rsimonsen's avatar
    rsimonsen
    Frequent Visitor
    6 years ago

    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's avatar
      ask4atish
      Icon for Advocate III rankAdvocate III
      6 years ago

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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