Forum Discussion

RobertSlattery's avatar
RobertSlattery
Icon for Responsive Resident rankResponsive Resident
8 years ago

Allselected function not removing row context for date hierarchy

Why does AllSelected not deliver the same behaviour in the two cases below?  startDate = CALCULATE( FIRSTDATE('Dim Date'[DateRaw].[Date]), ALLSELECTED('Dim Date'[DateRaw].[Date]) )
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi RobertSlattery

 

I wasn't trying to suggest a solution to a problem you were grappling with.  I was just trying to explain why the two charts you posted returned different results for the same query.   My suggestion was just to show how you might modify the syntax if you wanted to align the two visuals.  Hopefully I was able to help shed some light on one of the finer details of DAX Data Modelling in Power BI. :)

 

The "filty hack" that you describe actually creates entire tables in the background.  Not just add columns (which I guess can be considered adding columns via a relationship).  It was added to Power BI to make life easier for newer users.  I'm not a fan and turn it off in the options when I'm building data models and always use my own date tables.

 

 

RobertSlattery's avatar
RobertSlattery
Icon for Responsive Resident rankResponsive Resident
8 years ago

Hi Phil_Seamark, your reply was indeed extremely helpful as I mentioned.

 

The only reason I use the Auto Date/Time feature is because the automatic hierarchy supports a Continuous X-axis like this...

 

  

but, if I roll my own hierarchy, it forces a Category axis...

 

which is anoying.

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    The axis is forced to Category mode when it's dealing with a non-Date/Time field.  The field you are using at the bottom level is Integer (Day of the month).  

     

    If you use a DateTime field the axis will auto-scale

    • RobertSlattery's avatar
      RobertSlattery
      Icon for Responsive Resident rankResponsive Resident
      8 years ago

      Yes I did try that as one of the many permutations and I got this...

       

      But after reading your suggestion I realised there was one more permutation: using the go to next level button instead of the expand down ne level button with this on the axis:

      This works for the lowest level but, not for the Month level...

       

      The months summarised without considering the years and even if you set the sort order to Month Number, still don't sort in order.  It's like they thought of everything and systematically eliminated the options to bump their frustrated customer KPI.