Forum Discussion
Tricky situation with intersecting counts AND condition with a non calendar period (pbix attached)
Hello all,
The title may be not be best at summarizing the issue but I have tried to explain in detail below.
I am writing a dax measure for Customer counts with different conditions across three categories namely paying subs, lost subs and new subs. Each of these three categories have additional line items. All intent to get counts based on the row's specific condition. The paying subs portion of DAX is working fine because it has simpler conditions however the lost subs and new subs is where it gets a bit complex. This is sort of like checking for customers with specific conditions across two different tables and criteria. There is only one DAX measure in use here and it needs to work for the other two categories as well. Data is at a monthly level and the periods are predefined.
Here is a high level generic explanation.
But there is more to it hence I have attached links to the (1) actual data in excel, (2) the pbix file and (3) an excel explanation flow of how to get to the counts. The Folder contains three items.
Generic Explanation:
Question: What is the count of customers who have a status of 4 in current period and also have a status of 5 in the prior period?
Answer: 2
Reason: Because only customers A4 and A9 seem to satisfy the condition hence the count is two. Reference two tables below.
| Current Period | |
| Customer | status code |
| A1 | 1 |
| A2 | 2 |
| A3 | 3 |
| A4 | 4 |
| A5 | 5 |
| A6 | 4 |
| A7 | 4 |
| A8 | 3 |
| A9 | 4 |
| A10 | 5 |
| Previous period | |
| Customer | status code |
| A1 | 1 |
| A2 | 5 |
| A3 | 3 |
| A4 | 5 |
| A5 | 5 |
| A6 | 4 |
| A7 | 4 |
| A8 | 3 |
| A9 | 5 |
| A10 | 5 |
| A11 | 1 |
| A12 | 2 |
| A13 | 5 |
| A14 | 5 |
| A15 | 5 |
6 Replies
EduardDAdvocate III
Why not to combine (merge in Power Query) all those period columns in one fact table? then you can use filter to get your counts.
Another option (don't know how many transaction you have, maybe it would not be the best option for super large tables): you can create one master table with all customer name, build relationships from each of your period tables to the master customer table (use M:M directional relationship). Then the filters you apply on your period tables will get projected to master customer table, so you can use count. Measure DAX would look like = calcaulte (count(customerID), Filter (Period1Table, Status = 4), Filter (PEriod2Table, Status =5)
Hi EduardD The generic example does not fully represent the scenario in pbix. In actuality there is only one non calendar date column in the customer table. Since this has to be driven by predefined periods and we have to take another table's help we will not end up with the expected result. The pbix file has less than 900k rows in each table for the purposes of looking into the issue. The full volume of data goes in millions of records. And it has to be calculated dynamically based on the period selection because the scope needs to look at only the intersection of counts. Hence this may not work.
Please review the attachments and let me know if you need any additional details
- danextian
Super User
Hi Super-PBI
Create an index column using M in your period table or you can use RANKX:
Index = RANKX(values('Fixed Month Periods'[Date_To]),[Date_To],,ASC)And then create this measure:
Customer Count = VAR __CURRENT = MAX ( 'Fixed Month Periods'[Index] ) VAR __PREV = __CURRENT - 1 VAR __CurrentCustomers = SUMMARIZE ( FILTER ( 'Customer History Table', 'Customer History Table'[Status_Code] = "4" ), 'Customer History Table'[Customer_Number] ) VAR __PrevCustomers = SUMMARIZE ( FILTER ( ALL ( 'Customer History Table' ), 'Customer History Table'[Status_Code] = "5" && RELATED ( 'Fixed Month Periods'[Index] ) = __PREV ), 'Customer History Table'[Customer_Number] ) RETURN COUNTROWS ( INTERSECT ( __CurrentCustomers, __PrevCustomers ) )Note: I havent tested the correctness of this measure due to the large amount of data in your sample pbix. I also modified the relationship involving period table to single direction from period to other tables.
Hi danextian First of all thank you for looking into this. This approach is excellent. I am able to see some of the values populate in this small 900k row data set. I have a bit more modifications to do and I will try to implement this on my larger dataset which has 10's of millions of rows. I will share my findings.
In context of the same previously uploaded 900k row data set, I am missing two things for a couple scenarios:
1) So far we have seen status code in both current and prior periods and counted accordingly for each row. There is one row that is defined in a different way. How can we modify this DAX to account for a scenario where we have to get "Count of Customers who have CURRENT PERIOD Status code = '5', and PRIOR PERIOD Status code does not exist" ? This is the last row item for "--->from Net new" in excel.2)For the "Lost Counts" row item, this is defined by sum of the below 4 rows which are nothing but the four individual rows under this category. With the help of dax you shared I am able to get individual row counts after intersection for each of the 4 individual rows under lost category. How can we sum up the outcome of the four variables and show it under lost counts in the same dax? I have uploaded a copy of the dax measure in a text file for your reference in the same folder.
Thanks and appreciate your insights on this
- danextian
Hi Super-PBI
Try these:
Customer Count = VAR __CURRENT = MAX ( 'Fixed Month Periods'[Index] ) VAR __PREV = __CURRENT - 1 VAR __CurrentCustomers = SUMMARIZE ( FILTER ( 'Customer History Table', 'Customer History Table'[Status_Code] = "4" ), 'Customer History Table'[Customer_Number] ) VAR __PrevCustomers = SUMMARIZE ( FILTER ( ALL ( 'Customer History Table' ), ( ISBLANK ( 'Customer History Table'[Status_Code] ) || 'Customer History Table'[Status_Code] = 5 ) && RELATED ( 'Fixed Month Periods'[Index] ) = __PREV ), 'Customer History Table'[Customer_Number] ) RETURN COUNTROWS ( INTERSECT ( __CurrentCustomers, __PrevCustomers ) )Lost Count = VAR __CURRENT = MAX ( 'Fixed Month Periods'[Index] ) VAR __PREV = __CURRENT - 1 VAR __CurrentCustomers = SUMMARIZE ( FILTER ( 'Customer History Table', 'Customer History Table'[Status_Code] = "4" ), 'Customer History Table'[Customer_Number] ) VAR __PrevCustomers01 = SUMMARIZE ( FILTER ( ALL ( 'Customer History Table' ), 'Customer History Table'[Status_Code] = "1" && RELATED ( 'Fixed Month Periods'[Index] ) = __PREV ), 'Customer History Table'[Customer_Number] ) VAR __PrevCustomers02 = SUMMARIZE ( FILTER ( ALL ( 'Customer History Table' ), 'Customer History Table'[Status_Code] = "2" && RELATED ( 'Fixed Month Periods'[Index] ) = __PREV ), 'Customer History Table'[Customer_Number] ) RETURN COUNTROWS ( INTERSECT ( __CurrentCustomers, __PrevCustomers01 ) ) + COUNTROWS ( INTERSECT ( __CurrentCustomers, __PrevCustomers02 ) )Lost Count = VAR __CURRENT = MAX ( 'Fixed Month Periods'[Index] ) VAR __PREV = __CURRENT - 1 VAR __CurrentCustomers = SUMMARIZE ( FILTER ( 'Customer History Table', 'Customer History Table'[Status_Code] = "4" ), 'Customer History Table'[Customer_Number] ) VAR __PrevCustomers = SUMMARIZE ( FILTER ( ALL ( 'Customer History Table' ), VALUE ( 'Customer History Table'[Status_Code] ) --use value to convert status code to number IN { 1, 2, 3, 4 } && RELATED ( 'Fixed Month Periods'[Index] ) = __PREV ), 'Customer History Table'[Customer_Number] ) RETURN COUNTROWS ( INTERSECT ( __CurrentCustomers, __PrevCustomers ) )
