Forum Discussion
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
- amitchandak
Super User
scurrp , I think you very similar to what I have done here
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k- scurrpFrequent Visitor
I think my biggest problem was not naming the problem correctly.
I tried implementing the proposed solution, with some strange results.
Knowing the correct name of the problem I went to daxpatterns -dynamic segmentation and was able to implement that pattern successfully.