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

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

        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