Forum Discussion

scurrp's avatar
scurrp
Frequent Visitor
5 years ago

DAX Calculatetable then filter

Hi, 
I'm trying to calculate the count of records that fall within range criteria.

My approach was to calculate the table of results that I need to count.
This is the CustomerID and the Visit%. Visit% this, in turn, is a measure of actual vs plan.
Then I have a junk dimension dimCustomerVisitCategory, with min and max range values.
The next step is where I get stuck.

I want to count the rows of the calculated table where the visit % is between the min and max range values.

The problem I'm finding is that 

1- all records are getting allocated to 1 category 

2 - the number of records is higher than expected ie 67 vs 8.

 

Thanks in advance for any assistance.

 

 

 

xx = 
var IIS = ISINSCOPE(dimCustomerVisitCategory[Customer Visit Categories])
var Mn = MIN(dimCustomerVisitCategory[MinRange])
var Mx = Max(dimCustomerVisitCategory[MaxRange])
VAR CustVisitAvg =
 CALCULATETABLE (
 ADDCOLUMNS (
 SUMMARIZE ( fact_CustomerVisits, fact_CustomerVisits[CustomerID] ),
 "Customervisitpc", [Visit %]))

var CR = COUNTROWS( FILTER(CustVisitAvg,[Customervisitpc] >= mn && [Customervisitpc] < mx) )+0
return
Switch( true(),
IIS, CR
)

 

 


 

2 Replies