Measure not ignoring filters?

Question

I need to count ID's from my Retail table, but for weird reasons, I can't have a relationship from my Dates table to my Retail table.&nbsp;I have the measure below which correctly picks up the min and max date integers from my Date table and stores them as variables. I want to then filter my retail table by those store variables while also ignoring any filters on the page (which are from the Date table). Am I doing something wrong with the below measure?&nbsp;Total YTD History = var mindate = CALCULATE(MIN('Dates'[dateInt]),ALL('Dates'[Period]),ALL('Dates'[Week Number])) var maxdate = CALCULATE(MAX('Dates'[dateInt]))
return CALCULATE(DISTINCTCOUNT('Retail'[ID]),ALL('Dates'[Period]),ALL('Dates'[Week Number]),FILTER('Retail','Retail'[dateInt]&lt;= maxdate &amp;&amp; 'Retail'[dateInt]&gt;=mindate))&nbsp;

az38 · Answer

Hi&nbsp;Anonymous&nbsp;
I think this part&nbsp;
 CALCULATE(DISTINCTCOUNT('Retail'[ID]),ALL('Dates'[Period]),ALL('Dates'[Week Number]),FILTER('Retail','Retail'[dateInt]&lt;= maxdate &amp;&amp; 'Retail'[dateInt]&gt;=mindate))
could be rewrited as
 CALCULATE(DISTINCTCOUNT('Retail'[ID]),
FILTER(ALL('Retail'),'Retail'[dateInt]&lt;= maxdate &amp;&amp; 'Retail'[dateInt]&gt;=mindate))

anonymous · Answer

Thanks for the reply&nbsp;az38&nbsp;. Using your suggestion gives me the same repeating value? Something off with a relationship? I guess I have some columns from a different table then Retail on my matrix visual. I pretty much just want to ignore any filters coming from my Date table.&nbsp;So I have filters on my page from my Date table. I want to ignore those filters, but use them in my measure to determine the min and max dates. Then filter the Retail table by those min and max dates.

az38 · Answer

Anonymous&nbsp;
you find min and max dates from date table with variables.
then you used it for filter fact table. what context do you want to use further?
could you give an example: data sample and desired output based on this sample?

anonymous · Answer

az38&nbsp;So my Date table is connected to a FiscalTransaction table by date. The Fiscal Transaction table is connected to an order table by ID (Both filter directions). The order table is finally connected to my Retail table by ID (One to One so both filter directions is on).&nbsp;I want my measure to pretty much ignore that whole chain above. I have filter selections on my page from the Date table as there are other measures that it works with.&nbsp;&nbsp;I need to use the filter selections on the page to determine my min and max dates. Then filter my retail table by those min and max dates, but ignore the relationship chain described above.&nbsp;&nbsp;I can tell what's happening in my measure. There are a number of ID's in my Retail table that are not in the Order table. So I think the measure is taking the dates from my date table based on the slicers on my page, then filtering the Fiscal Transaction table by those dates. Then the Fiscal Transaction table has a set of ID's which it is then filtering the Order table by ID. Then the Order ID table is filtering my Retail table by those same ID's resulting in a much lower number. I want my measure to ignore the whole chain I just described as there are a number of ID's that are present in the Retail table that are not on the Order table.&nbsp;&nbsp;I can verify this is the case because if I remove all the slicers on the page from my Date table and manually filter my Retail table by the min and max dates, I get the correct number. As soon as I put a slicer on my page from the Date table, the number goes way down. So the slicer from the Date table is having an impact on my measure when I don't want it to.

Forum Discussion

Measure not ignoring filters?

4 Replies

Related content

Make measure ignore specific filter

Ignore filter for a measure

Power BI – YoY Measure Ignoring Product Filter but Respecting Slicers

DAX Measure ignore filter/slicer selection

Creating a measure that is the value of another measure but ignores a filter