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,

 

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

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

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

      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. 

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