Forum Discussion

ask4atish's avatar
ask4atish
Advocate III
6 years ago
Solved

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

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
    Advocate 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
      Community 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

      • ask4atish's avatar
        ask4atish
        Advocate III
        6 years ago

        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