Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Showing zero usage in Phone Vendor data

Hi everybody, 

 

I have an issue with my report. 

Here is my dataset:

All of the columns come from an SQL database. Total usage is a calculated column that contains the sum of total text, total data, and voice minutes. 
I have to show the number of phone number what have zero usage. At this point this is a usual distinct count function. But this distinct count has to be related to the billing period. 

For example : If I choose the 2020 February and march in the date slicer (this date slicer is on the page) the card visualization must to show "2" because two phone numbers had zero usage both of selected month. So if I choose more than one month the relation has to "and" not "or". 

 

Can anybody help me? 

 

Thank you, 

Anett 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    You can try my way. Firstly, add a unrelated Month table to build a slicer.

    My Sample:

    Measure:

    Zero Usage = 
VAR _MONTH =
    VALUES ( 'Month'[MonthNo] )
VAR _COUNTSELECT =
    COUNT ( 'Month'[MonthNo] )
VAR _T =
    ADDCOLUMNS (
        SUMMARIZE ( Sheet55, Sheet55[Phone number] ),
        "count",
            CALCULATE (
                COUNT ( Sheet55[Phone number] ),
                FILTER (
                    Sheet55,
                    Sheet55[Phone number] = EARLIER ( [Phone number] )
                        && MONTH ( Sheet55[Billing period] )
                            IN _MONTH
                            && Sheet55[Total Usage] = 0
                )
            )
    )
RETURN
    COUNTAX ( FILTER ( _T, [count] = _COUNTSELECT ), [Phone number] ) + 0

    Result is as below.

    Select February:

    Select February and March:

    Best Regards,

    Rico Zhou

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Create a measure like this and use it with the phone number

     

    calculate(count(Table[phone number]), filter(Table, Table[total usages] =0))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This is not what I searching for.
      I need the distinct count of the phone numbers where the SUM total usage =0 in the selected period. 

      Thank you, 

      Anett 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous 

        You can try my way. Firstly, add a unrelated Month table to build a slicer.

        My Sample:

        Measure:

        Zero Usage = 
VAR _MONTH =
    VALUES ( 'Month'[MonthNo] )
VAR _COUNTSELECT =
    COUNT ( 'Month'[MonthNo] )
VAR _T =
    ADDCOLUMNS (
        SUMMARIZE ( Sheet55, Sheet55[Phone number] ),
        "count",
            CALCULATE (
                COUNT ( Sheet55[Phone number] ),
                FILTER (
                    Sheet55,
                    Sheet55[Phone number] = EARLIER ( [Phone number] )
                        && MONTH ( Sheet55[Billing period] )
                            IN _MONTH
                            && Sheet55[Total Usage] = 0
                )
            )
    )
RETURN
    COUNTAX ( FILTER ( _T, [count] = _COUNTSELECT ), [Phone number] ) + 0

        Result is as below.

        Select February:

        Select February and March:

        Best Regards,

        Rico Zhou

         

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