Line Chart Visual Disregarding Filters Once Date is Added to X-Axis

[karleek]

I have a measures that work to identify #1: the amount of months within my date slicer, if it was May 2024 - July 2024 that answer is 3 and then #2 if the customer had orders across that same number of months, looks at order table and if they had orders in May, June and July then that customers measure is 3, if those 2 measures match then a 3rd measure shows 1, if they don't match it shows 0.

Those measures are working perfectly, when I put all the data in just a standard Table and filter for measure #3 =1, it shows all data properly.

However, when I put the data onto a Line Chart and then try to add Date to the X-Axis it completely disregards the filter for measure #3. I cannot figure out why for the life of me, nor can a colleague that is very skilled in data. Any ideas here? 

@Ahmedx @@AlexisOlson @amitchandak @Ashish_Mathur @danextian @Fowmy @Greg_Deckler @Jihwan_Ki@lbendlin @parry2k @talespin @tamerj1 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result there.

[v-cgao-msft]

Hi @karleek ,

Context ultimately affects the counting results of the metric. You may have initially debugged it in the context containing the date. I created a sample model:

NumberOfMonths = CALCULATE( DATEDIFF(MIN('Date'[Date]), MAX('Date'[Date]), MONTH) + 1, ALL('Orders'[CustomerID]),ALLSELECTED('Date'[Date]))

CustomerOrderMonths = CALCULATE(DISTINCTCOUNT('Date'[Month]),TREATAS(CALCULATETABLE(VALUES('Orders'[OrderDate]),ALLEXCEPT('Date','Date'[Month])),'Date'[Date]))

MatchFilter = 
IF(
    [NumberOfMonths] = [CustomerOrderMonths],
    1,
    0
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[karleek]

UPDATE: The visual works properly is I only put in YEAR for the date, but if I try to add in Month or even just add in Month Year that is when it started ignoring the Match filter. Any ideas? @v-cgao-msft 

@v-cgao-msft Thank you for this solution. I had to add a KeepFilter in the Customer Order Month measures since my date slicer will span over 1 year, but otherwise the measures worked. BUT my visual still is not recognizing the Match Filter, it is continuing to display all customers regardless of whether the Match Filter = 1 or not. 

[v-cgao-msft · Accepted solution]

Hi @karleek ，

I made some changes to the measures:

NumberOfMonths = CALCULATE( DATEDIFF(MIN('Date'[Date]), MAX('Date'[Date]), MONTH) + 1 , ALLSELECTED('Date'))

CustomerOrderMonths = 
    CALCULATE(
        DISTINCTCOUNT('Date'[Month]),
        FILTER(
            ALL('Date'), 
            COUNTROWS(RELATEDTABLE('Orders')) > 0
        ),
        VALUES('Orders'[CustomerID])
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum