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

[Raketenrudi]

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	[Not relevant]

Table 'Calls'

Call ID	Customer ID	Date	Product
1	A	JAN	XYZ
2	B	MAR	XYZ
3	C	FEB	XYZ
4	A	FEB	UVW
5	A	JAN	XYZ

What was fairly easy was to relate the tables by 1:n based on the customer ID in the Desktop environment. The tables are added from two different semantic models. With the 'standard' operations and a simple measure (counting distinct Call IDs) I could easily create the following table.

Customer ID	Last Call Date	Call count
A	FEB	3
B	MAR	1
C	FEB	1

Of course the entries for customer D are missing, since there are no calls. Usually this is the desired behaviour. In this case however, I would like to see this result - showing blanks/zeros/etc for the customers without calls:

Desired table

Customer ID	Last Call Date	Call count
A	FEB	3
B	MAR	1
C	FEB	1
D	none	0

This should work also with the date slicer (and/or product slicer). It surely works at the moment, but does also remove all customer rows without calls. 

I should add that the customer table has 10k+ entries and the call tables has 500k+ entries. So a performance friendly approach should be considered. 

Based on the desired table I would then like to calculate the coverage as 'customer contacted' / 'total customers'. In the case above: coverage = 3/4 = 75%

Thanks a lot 🙂

[Raketenrudi]

Hi @Elena_Kalina @FBergamaschi @v-sshirivolu ,

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 🙂

[v-sshirivolu]

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

[v-sshirivolu]

Hi @Raketenrudi ,
Thank you for reaching out to Microsoft fabric community forum.

Follow these steps to achieve your desired result - 
Create Relationship

Drag Customer[Customer ID] to Calls[Customer ID]

Keep it 1 to many, single direction

Create Measures - 
Call Count Measure

Call Count :=
CALCULATE(
COUNTROWS('Calls'),
FILTER(
ALL('Calls'),
'Calls'[Customer ID] = SELECTEDVALUE('Customer'[Customer ID])
)
)

Last Call Date Measure
Last Call Date :=
VAR LastDate =
CALCULATE(
MAX('Calls'[Date]),
FILTER(
ALL('Calls'),
'Calls'[Customer ID] = SELECTEDVALUE('Customer'[Customer ID])
)
)
RETURN
IF(LastDate <> BLANK(), LastDate, "none")

Coverage Measure
Coverage % :=
VAR TotalCustomers = COUNTROWS(Customer)
VAR ContactedCustomers =
CALCULATE(
COUNTROWS(Customer),
FILTER(Customer, [Call Count] > 0)
)
RETURN
FORMAT(DIVIDE(ContactedCustomers, TotalCustomers), "0.0%")

Create Visual
Insert a Table visual

Add these fields from Customer table:

Customer ID

[Last Call Date] (measure)

[Call Count] (measure)

Add Slicers (Optional)
Add slicers for Calls[Date] and/or Calls[Product]
The logic will dynamically reflect the customer call history as per slicer filter.

Add KPI Card for Coverage
Add a Card visual - keep [Coverage %]

Please find the below attached .pbix file for your reference.

Regards,
Sreeteja.

[v-sshirivolu]

Hi @Raketenrudi ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[Elena_Kalina · Accepted solution]

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.

[FBergamaschi]

First point is to obtain this values in the visual, right?

Desired table

Customer ID	Last Call Date	Call count
A	FEB	3
B	MAR	1
C	FEB	1
D	none	0

You need to select "Show items with no value"

Then, for the calculation you need, you can write this measure

DIVIDE (

      DISTINCTCOUNT ( Fact[Customer] ),
      COUNTROWS ( Customer )
)

Pay attention to one detail, should you apply filters to a dimension (ex a period selected in the Calendar ), this would affect only the DISTINCTCOUNT ( Fact[Customer] ) and not the COUNTROWS ( Customer ) so this calculation whould be used carefully

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI