Forum Discussion
Dynamic group count
Hi paweldm,
I have made some tests with your data and come up with this formula, the slicer is calculated based on the Year column without any relationship with the Table 1.
Count_ID = CALCULATE ( COUNT(Table1[ ownerId ]), FILTER ( Table1, Table1[ DateHistFrom ].[Year] <= MAX(Table2[Year]) && Table1[ DateHistFrom _1]<>BLANK() ) )
In this calculation I have come up with the result below:
One question do you also want that the tables with the details to be filter by the slicer?
Regards,
MFelix
Hi MFelix
MEASURE COMMENTS:
Thank you for the measure.
I believe a small change in the DAX code is required since count function should calculate distinct pairs of dates: histFrom & histTo and not distinct owners.
I am giving some more explanation to my example:
1 | A | 2010-01-01 | 2017-01-01 |
1 | B | 2010-01-01 | 2017-01-01 |
1 | C | 2012-01-01 | 2017-01-01 |
1 | D | 2017-01-01 | 'blank' |
2 | E | 2010-01-01 | 2016-01-01 |
2 | F | 2016-01-01 | 2017-01-01 |
There are 3 distinct pairs of dates for pharmaId = 1 (2010-01-01 & 2017-01-01 // 2012-01-01 & 2017-01-01 // 2017-01-01 & 'blank') and 2 distinct pairs of dates for pharmaId = 2 (2010-01-01 & 2016-01-01 // 2016-01-01 & 2017-01-01).
In other words, there were 2 owners (A&B) of pharmaId=1 from 2010-01-01 to 2017-01-01 [1st set] and there were 3rd owner (C) of pharmaId=1 from 2012-01-01 to 2017-01-01 [2nd set]. And finally owner A, B, C sold their shares for D owner and there was one owner of pharmaId=1 from 2017-01-01 to now [3rd set].
So there are 3 different ownerSets of ownership over the lifetime of pharmacy with pharmaId =1
In case 2010, 2011, 2012, 2013 from Tabel 2 are selected on the slicer, there are 2 distinct pairs of dates for pharmaId = 1 which comply with the slicer selection (2010-01-01 & 2017-01-01 ; 2012-01-01 & 2017-01-01).
So there are 2 different ownerSets of ownership over the filtered lifetime of pharmacy with pharmaId =1. Your measure gives value of 3.
ANSWER FOR YOUR QUESTION:
It could be also useful to filter Table 1 by the date slicer as well, but I believe I need to have another date table connected with Table 1 to do that.
Pawel