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
Hi paweldm,
First of all my columns were the same name but that is already solved. I have made this change to the
Add a column with the following calculation
Period = IF( Table1[ DateHistTo]=BLANK(), DATEDIFF(
Table1[ DateHistFrom ],
TODAY(),
DAY
), DATEDIFF( Table1[ DateHistFrom ], Table1[ DateHistTo], DAY ) )Redo you measure to this
Count_ID = CALCULATE ( DISTINCTCOUNT(Table1[Period]), FILTER ( Table1, Table1[ DateHistFrom ].[Year] <= MAX(Table2[Year]) ) )
Result is this in my test:
Regards
MFelix
Hi MFelix,
I have looked into your measure and I wanted to ask you if your Count_ID calculates 2 different periods which are equally in terms of number of days as one or two unique owner sets count?
Example:
There are 2 periods = 60 days each. Additionally each period has a different set of DateHistFrom & DateHistTo.
It seems that Count_ID will not calculate the periods as two different as it calculates distinctcount of [Period] and it will return value of 1.
Please give me your comment.
Pawel
Hi paweldm,
Didn't realize that because the amount of data was limited change the column Period to this formula everything else should work ok.
Period_1 = IF( Table1[DateHistTo]=BLANK(), Table1[DateHistFrom]&TODAY(), Table1[DateHistFrom]&Table1[DateHistTo] )
This will give you unique counts per periods since you are defining the start and end as unique text value so if you have periods with the same number of days they will not be the same.
Regards,
MFelix