Forum Discussion
Issue filtering a FullDate Hierarchy
Hey, thanks for the reply.
My D_FilteredDate is a calculated table from my original date dimension.
My field DateFull is modified as a Date/Time Type.
My Last12Months is a flag calculated in my D_FilteredDate using:
Last12Months = (IF(D_FilteredDate[DateFull].[Date]>EOMONTH(NOW();-13) && D_FilteredDate[DateFull].[Date]<=EOMonth(NOW();-1);1;0))
I'll try and explain the full calculations behind the graph:
We have the numerator:
AffectedArticlesLastTwoMonths = CALCULATE(
SUMX(FILTER(F_Defects_TicketOwner;[Quantity] >= 0); F_Defects_TicketOwner[Quantity]);USERELATIONSHIP(F_Defects_TicketOwner[ContactDateId];D_FilteredDate[DateId]);
DATESBETWEEN(D_FilteredDate[DateFull].[Date];
FIRSTDATE(DATEADD(D_FilteredDate[DateFull].[Date]; -2; MONTH));
LASTDATE(DATEADD(D_FilteredDate[DateFull].[Date]; -2; MONTH))
))
We have the denominator:
ProductsInvoicedPrev12Monthsv3 = CALCULATE (
SUMX(FILTER(F_Sales;[InvoiceQuantity] >=0);[InvoiceQuantity]);
DATESBETWEEN(D_FilteredDate[DateFull].[Date];
FIRSTDATE(DATEADD(D_FilteredDate[DateFull].[Date];-13;MONTH));
LASTDATE(DATEADD(D_FilteredDate[DateFull].[Date];-2;MONTH))
))
The fraction is:
Market Defects = ('F_Defects_TicketOwner'[AffectedArticlesLastMonth]/('F_Sales'[ProductsInvoicedPrev12Monthsv3]/1000000))
In essence the fraction takes two different data ranges and based upon that it makes it's calculation.
I hope this might shed some more light into my problem. Getting sample data isn't that easy for me to do. If alas it doesn't solve it, i'll put in some extra work into manufacturing some masked data.
Tyvm in advance.
Hi NielsDecoene,
What date values does the DateFull field contain? Based on my test, if the DateFull field contains date values from Jan 2016 to Dec 2016, it will show all the values in the slicer once you drag the field into custom Hierarchy Slicer.
In addition, do you create a visual using Market Defects and DateFull field? If that is the case, when you drag Last12Months into another Hierarchy Slicer, how does the visualization perform?
Thanks,
Lydia Zhang
- NielsDecoene9 years agoHelper I
Hey Anonymous
As said the D_FilteredDate comes from D_Date (typical date dimension created from stored procedure)
the D_FiltereDate is a piece of D_Date it starts at a specific day in time (somewhere 2011) and it changes dynamically to
the last full month that's come around, so currently we're 09/11/2016 it will contain everything till 31/10/2016.
The graph has
Market Defects as Value and as Axis Datefull (Year Month)
If i take the working version and i create a second hierarchy slicer also impacting this visual:
It changes the values on each month to something incorrect.
I will try and make some data during lunchbreak.
- Anonymous9 years agoNot applicable
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- NielsDecoene9 years agoHelper I
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