Forum Discussion
Dynamic group count
Hej PowerBi Community!
I need your help to write the DAX measure.
Input tables:
Table 1: pharmacy table includes columns:
| pharmaId | ownerId | DateHistFrom | DateHistFrom |
Table 2: date table includes columns:
| Year |
I belive table 2 should not be connected with table 1 in order to properly filter table 1, but I am not sure.
Required measure:
# ownerSet - calculates the number of owner sets over time grouped by distinct periods defined by DateHistFrom & DateHistFrom together and by pharmaID. The measure can be filtered by date table as well.
Example:
Table 1
| pharmaId | ownerId | DateHistFrom | DateHistFrom |
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 |
Table 2
| Year |
2010
2011
2012
2013
2014
2015
2016
2017
Manually calculated ownerSets:
pharmaId = 1
1st set:
1 | A | 2010-01-01 | 2017-01-01 |
1 | B | 2010-01-01 | 2017-01-01 |
2nd set:
1 | A | 2010-01-01 | 2017-01-01 |
1 | B | 2010-01-01 | 2017-01-01 |
1 | C | 2012-01-01 | 2017-01-01 |
3rd set:
1 | D | 2017-01-01 | 'blank' |
pharmaId = 2
1st set:
2 | E | 2010-01-01 | 2016-01-01 |
2nd set:
2 | F | 2016-01-01 | 2017-01-01 |
Output tabel:
All years from Tabel 2 are selected on the slicer:
[pharmaId] | [#ownerSet]
1 | 3 |
2 | 2 |
In case 2010, 2011,2012, 2013 from Tabel 2 are selected on the slicer:
[pharmaId] | [#ownerSet]
1 | 2 |
2 | 1 |
Thank you in advance!
Pawel
9 Replies
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
