Forum Discussion

vickyw's avatar
vickyw
Icon for Helper I rankHelper I
1 year ago
Solved

Column chart not filtering Table viz due to USERELATIONSHIP() measure

Hi All,

 

Hoping someone can help. I suspect I have caused an issue due to my inexperience at modelling.

My model has a date table (marked as a date table) and time intelligence turned off, along with 4 fact tables

  • Cases
  • Appointments
  • Sessions
  • Forms

 

The 'Cases' table has the only active relationship to the date table. The other 3 are inactive relationships.


The page below shows data from the 'FORMS' table. Both the column chart and card visual contain the following measure:

 

Total Forms by Updated Date = CALCULATE(DISTINCTCOUNT('Forms'[Form ID]), USERELATIONSHIP('Date Table'[Date],'Forms'[Last Updated Date]))



 

When I click on the column for January, sure enough the card visual filters down from 229 to just 9 however, the table visual is uneffected and still shows the full 229 forms.

I am hoping there is a way for the column chart to filter the table visual also.

 

Or if there is a better way to model with mutiple fact tables, any suggestions are appreciated.

I have uploaded an anonymised stripped down example here:
Anon Version 2.pbix

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    1 year ago

    Add your measure to count the forms so the table has someting to filter.  

    Also, There is a filter on your table visual that is limiting it to between 1/1/2025 and 1/31/2025

     

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    1 year ago

    Add your measure to count the forms so the table has someting to filter.  

    Also, There is a filter on your table visual that is limiting it to between 1/1/2025 and 1/31/2025

     

    • vickyw's avatar
      vickyw
      Icon for Helper I rankHelper I
      1 year ago

      I love & appreciate this forum so much. It never ceases to amaze me that I can post a question after work one night, and by the next morning I have a solution.

       

      Thank you so much both.

       

      I dragged the measured used in the other 2 visuals to the table also, and it is now filtering from clicks on the column chart 🙂

      (Ah yes, apologies I was trying to filter the table from the filter pane also, and forgot to remove this from my sample workbook before posting it ğŸ˜–)


      Column chart now filtering table:

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi vickyw ,

    Based on the testing, creating the new column in Forms table.

    Year and month = FORMAT(Forms[Last Updated], "mmm yyyy")

    Then, creating the measure to filter the table visual.

    Measure = 
var _date = SELECTEDVALUE('Date Table'[Month & Year])
var _forms = SELECTEDVALUE(Forms[Year and month])
RETURN
IF(_date = _forms, 1, 0)

    Drag the measure to the table visual Filters pane and set the show item is 1.

     

    Best Regards,

    Wisdom Wu

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