Forum Discussion

thrillhouse's avatar
thrillhouse
Icon for Helper I rankHelper I
5 years ago
Solved

Ignore Visual Level Filter

Hello,

 

I have a data set with monthly totals of subscribers.  I am using a max date filter to display a bar chart with only current month totals by user type.  I also have a line chart to show growth over time.  The problem I have run into is that if I want to use the user type bar chart to filter the growth chart (to view growth within specific user types), the max date filter on the user type chart causes the growth chart to only plot the current month (max date).  I have tried to use the below DAX expression in the line chart to ignore the max date, but have not had any luck with it:
TotalSubscribers_Fixed = calculate(SUM('Table'[End of Period Subscribers]),ALL('Table'[Max Date]))
 
any help would be greatly appreciated!
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi thrillhouse 

    I think you want to filter your Line chart by bar chart. Your bar chart should add user type(A,B) on Axis and Legends, then you select A, Line chart will only show values for A.

    My Sample:

    ...

    I build a bar chart and let it show the values in MaxDate's month by measure.

    Values in MaxdateMonth = 
VAR _MaxDate = MAXX(FILTER(ALL(Sheet38),Sheet38[User]=MAX(Sheet38[User])),Sheet38[Date])
VAR _MAXDateYear = YEAR(_MaxDate)
VAR _MAXDateMonth = MONTH(_MaxDate)
Return
SUMX(FILTER(Sheet38,YEAR(Sheet38[Date]) = _MAXDateYear&&MONTH(Sheet38[Date]) = _MAXDateMonth),Sheet38[Value])

    Bar chart:

    Then build a Line chart and have a test.

    By default:

    When I select A in bar chart, my line chart will show all values for A.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    thrillhouse ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
    Appreciate your Kudos.

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    If I may add to what's been said in this thread:

    It is understandable that members don't want to share their Pibx file for security reasons. But it is very time consuming for the community to quickly understand the issue and respond faster. For this I suggest attaching or uploading (Onedrive, Google dirve, Dropbox...) a Dummy Pbix file. 

    Here is how to make a dummy Pibx file:

    - Save your Pbix file under Dummy.pbix

    - Locate the tables that are related to the issue and Delete the rest.

    - On the remaining tables delete all columns that are not related to the issue

    - On the remaining columns select a few rows that are related to this issue and delete the rest

    - If some sensitive datas are still there then replace them by XXXXXX for an example.

     

    By doing so you not only help us understand better your issue but also to get it solved many times faster.

  • thrillhouse's avatar
    thrillhouse
    Icon for Helper I rankHelper I
    5 years ago

    Hi amitchandak,

     

    It's a little difficult for me to dummy up sample data. But my problem lies in that the only field I have to display totals is an "end of month total subscribers".  So if I want to show current totals, I have to use a max date.  Then, to plot each month's totals to show growth, I am unable to use the chart with current totals by user type to filter the growth chart by user type because the max date filter from the usertype current totals chart carries over into the growth chart and it now only plots the max date totals.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi thrillhouse 

      I think you want to filter your Line chart by bar chart. Your bar chart should add user type(A,B) on Axis and Legends, then you select A, Line chart will only show values for A.

      My Sample:

      ...

      I build a bar chart and let it show the values in MaxDate's month by measure.

      Values in MaxdateMonth = 
VAR _MaxDate = MAXX(FILTER(ALL(Sheet38),Sheet38[User]=MAX(Sheet38[User])),Sheet38[Date])
VAR _MAXDateYear = YEAR(_MaxDate)
VAR _MAXDateMonth = MONTH(_MaxDate)
Return
SUMX(FILTER(Sheet38,YEAR(Sheet38[Date]) = _MAXDateYear&&MONTH(Sheet38[Date]) = _MAXDateMonth),Sheet38[Value])

      Bar chart:

      Then build a Line chart and have a test.

      By default:

      When I select A in bar chart, my line chart will show all values for A.

       

      Best Regards,

      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.