topic Re: DAX Calculatetable then filter in DAX Commands and Tips

DAX Calculatetable then filter

scurrp — Wed, 25 Aug 2021 05:49:28 GMT

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 )

Re: DAX Calculatetable then filter

amitchandak — Wed, 25 Aug 2021 06:02:42 GMT

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

Re: DAX Calculatetable then filter

scurrp — Wed, 25 Aug 2021 22:23:32 GMT

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.