Dynamic group count

[paweldm]

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

[MFelix]

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

[paweldm]

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

[MFelix]

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

[paweldm]

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

[MFelix]

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

[paweldm]

HI @MFelix,

One more question how should the Count measure be modified to reflect the option of sorting dates not only by the max of the date[year] as it is now but also to enable sorting  by min date[year] (for example set the period on the slicer 2012-2013) .

Thx a lot !

P. 

[paweldm]

Hi  @MFelix

I need to filter the measure based on whichever selection of histfrom & histto date sets.

What's important if I select in the first step the histFrom dates range, I should be able to only logically select histTo dates ranges with the condition that histTo>histFrom

e.g. hypothetically I want to filter the  Count_ID measure based on the 2 criteria:

- 1. histFrom is from 2011 to 2013 

- 2. histTo is from 2012 to 2016 ( I should not be able to select the range from: 2010 to 2016)

Regards,

Pawel

[MFelix]

Hi @paweldm,

You want that the information you have to be filter accordingly to the Year on the HistFrom, for this maybe the best way is to add a column in the Table 1 with the formula Year = Table1[DateHistFrom].[Year] then make a relationship between this column and the year column so you information will always be filter accordingly to the start date.

MFelix

[MFelix]

Hi @paweldm,

Do you want to make the measurw change accordingly to be from the histfrom or histto accordingly to user selection? Or am I not understanding your question

Mfelix