Forum Discussion

paweldm's avatar
paweldm
Helper II
9 years ago

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 |       T...
Need Help
MFelix's avatar
MFelix
Super User
9 years ago

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's avatar
    paweldm
    Helper II
    9 years ago

    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's avatar
      MFelix
      Super User
      9 years ago

      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's avatar
        paweldm
        Helper II
        9 years ago

        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