Forum Discussion
Raketenrudi
1 year agoRegular Visitor
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 ...
- 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.
Elena_Kalina
Solution Sage
1 year agoHi 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.