Forum Discussion

NielsDecoene's avatar
NielsDecoene
Helper I
9 years ago

Issue filtering a FullDate Hierarchy

I have a problem while making a report on a Data Warehouse I have 4 visuals with calculations that play with Date/Time example: AffectedArticlesLastTwoMonths = CALCULATE( SUMX(FILTER(F_Defects_Tic...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi NielsDecoene，

I test your scenario using my data, and I can now reproduce your issue when using hierarchy slicer and the issue still occurs when I drag Last12Months into another Hierarchy Slicer.

I make a test using Power BI Desktop built-in slicer and use the slicers to filter the visual, everything works well, you can have another try with built-in slicer and check if your visual performs well.

In a word, I think the issue is more related to the custom hierarchy slicer, I would recommend you directly contact the author to get support. In this link, you can send an email to the author by clicking “Contact Author”.

Thanks,
Lydia Zhang

NielsDecoene's avatar
NielsDecoene
Helper I
9 years ago

Hi Anonymous

 

I've been able to reduce the problem to it's core, after modifying some measures with ALL(DateFull) i can keep the calculations showing correctly. However i can not yet filter the hierarchy based on a flag.

 

Let's say you have a flag [1,0] on your date dimension.  The flag is true on the last 12 full months.

 

Context you put the DateFull (Date/Time model) in a tabel, you take the hierarchy. 

If you try and filter this table using the option 1 or 0 it will not affect the table what so ever. 
A custom hierarchy does get filtered but my dashboards are not made based on custom hierarchies....

 

Is it something special with Power Bi's standard hierarchy preventing the filtering from occuring?

 

Kind regards Niels Decoene

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi NielsDecoene,

    How do you modify your measure with ALL(DateFull) ? In Power BI, ALL() function ignores all slicers and Visual/Page/Report filters. 


    Thanks,
    Lydia Zhang

    • NielsDecoene's avatar
      NielsDecoene
      Helper I
      9 years ago

      Hey Anonymous

      Because, the measures are based on DateFull:

      -We have the measure being sliced by the X-Axis (DateFull)

      -We have the graph being filtered by the Slicer 

       

      So that concludes our DateFull being sliced from two points of origin to avoid Blank(s) to a degree we're trying to let the measures run correct if the data being supplied to the graph get's cut off to a certain degree.

       

      An example :

      We filter data (Visual level Filter 12 months) that means the only data my measure can access is those 12 months,
      but if we have calcs going back in time we're crossing that boundary. Which results in 0 being returned by the measure.
      By supplying ALL in the calculations we're giving our reach the access of our entire dimension.

      So even if we want to view the data for the month, that's a year a go the measure still has those dates to work with.

       

      I hope i'm being somewhat clear about it.

       

      P.S. I've contacted the author about filtering the last 12 months out of it. It should be by adding my Flag to the values property of the graph. But alas even that does not work he'll look into it and try to push a fix in the next release.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi NielsDecoene,


        Would you mind sharing me the PBIX file or relevant screenshots via Private message? From Private message, I note that you have a new issue about Date Hierarchy, I test the TOP N filter and cannot reproduce your issue.

        Thanks,
        Lydia Zhang