Forum Discussion
Table with 'blanks' and calculating 'Coverage' based on customers, calls and time
- 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.
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.
- Anonymous1 year agoNot applicable
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.