Forum Discussion

Raketenrudi's avatar
Raketenrudi
Regular Visitor
1 year ago
Solved

Table with 'blanks' and calculating 'Coverage' based on customers, calls and time

Hi,    There are two tables I'd like to work with and a date slicer.    Table 'Customer' Customer ID Customer Infos ... A [Not relevant] B [Not relevant] C [Not relevant] D ...
Tips and Tricks
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    Hi Raketenrudi 

    Try these measures

    Last Call Date = 
VAR CustomerID = SELECTEDVALUE(Customer[Customer ID])
VAR MaxMonthNumber = 
    CALCULATE(
        MAX(Calls[MonthNumber]),
        FILTER(
            ALLSELECTED(Calls),
            Calls[Customer ID] = CustomerID
        )
    )
VAR LastMonthName = 
    SWITCH(
        MaxMonthNumber,
        1, "JAN", 2, "FEB", 3, "MAR", 4, "APR",
        5, "MAY", 6, "JUN", 7, "JUL", 8, "AUG",
        9, "SEP", 10, "OCT", 11, "NOV", 12, "DEC",
        "нет"
    )
RETURN
    IF(ISBLANK(MaxMonthNumber) || MaxMonthNumber = 0, "none", LastMonthName)
    Call Count = 
VAR CustomerID = SELECTEDVALUE(Customer[Customer ID])
VAR Result = 
    CALCULATE(
        DISTINCTCOUNT(Calls[Call ID]),
        FILTER(
            ALLSELECTED(Calls),
            Calls[Customer ID] = CustomerID
        )
    )
RETURN
    IF(ISBLANK(Result), 0, Result)

     

    Coverage = 
VAR CustomersWithCalls = 
    CALCULATE(
        DISTINCTCOUNT(Customer[Customer ID]),
        FILTER(
            Customer,
            [Call Count] > 0
        )
    )
VAR TotalCustomers = 
    DISTINCTCOUNT(Customer[Customer ID])
RETURN
    DIVIDE(CustomersWithCalls, TotalCustomers, 0)

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

    Thank you.

     

Raketenrudi's avatar
Raketenrudi
Regular Visitor
1 year ago

Hi Elena_Kalina FBergamaschi Anonymous ,

 

 

thank you for sharing your ideas. The combination led me to the solution of the problem. 

 

All of your proposals work for the small table - but since I have this big table, the CALCULATE expressions does not work, if I want to show them in the table. I.e. the call count including zeros. I get the error message 'exceding ressources'. BUT it works if applied to the general filter setting of the visual.

 

  1.  Use a one directional relation from table 'customer' to table 'calls'.
  2. Set up a straightforward measure for coverage: DISTINCTCOUNT(calls[Customer ID] )/ DISTINCTCOUNT(customers[Customer ID]). This works due to the above mentioned relation. (Note: Nesting in to a CALCULATE expression defining the cross filter method is also working)
  3. To show the entries of customers with no calls - considering all other filters applied - I set up another measure 'check_if_exists' evaluating an IF statement. The statement returns 'N' if no calls exist. 
  4. Apply 'check_if_exists' to the visual filter settings. The result is a table containing only customers with zero calls. (Note: The measure cannot be applied to the visual itself, as I wanted in the beginning, due to the size of the table)

Thanks again and hopefully it helps somebody else out there as well 🙂

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Raketenrudi ,

    Great to hear the issue is resolved.
    Thanks for sharing your approach, it will surely help others too. If everything’s working as expected, you may consider marking the thread as complete.

    Best,
    Sreeteja
    Community Support Team