Forum Discussion

karleek's avatar
karleek
Frequent Visitor
2 years ago
Solved

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

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 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      karleek
      Frequent Visitor
      2 years ago

      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? Anonymous 

      Anonymous 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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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