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.
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