Filter Data from Multiple Tables

[AndyMills]

Dear Group:

I tried reading other forum posts to my issue, but wasn't able to find an answer so I am hoping one of you can help.  

I have a tab with a table and a date slicer, in that the user can pick a start / end date.  I am displaying the following columns from two tables (with the tables listed in the parenthesis). 

• Site (Control Table as this value is in both tables and the link between tables)
• Calls Answered (Table A)
• Accepts (Table B)
• Declines (Table B)

I need to filter this table when Calls Answered (from Table A) has a value of zero.  I know I can create a filter, but when I do this, it does filter the records for Table A, but Table B values are still shown and not filtered.  

How would one go about adding a filter that takes into account the date range when we need to not display rows when Calls Answered is zero when this value is only in the one table?  

[Icey]

Hi @AndyMills ,

Please let us know which reply is the solution. Your contribution is highly appreciated.

Best Regards,

Icey

[Greg_Deckler]

@AndyMills I am in agreement with @PaulDBrown you are probably missing a relationship or something so we would have to see your model to be of any help.

[PaulDBrown]

@AndyMills 

Can you post the screenshot for your model (and describe which field(s) establish the relationships ? Or even better, provide a sample of your datset (hide sensitive info) or a PBIX file?

[AndyMills]

Thank you for the help.  I am hoping these screenshot help show the issue more clearly.  I didn't include the entire model, just the three tables in question.  

qry_CMS is the table that hold "Calls Answered", which is column "Calls".  But I also display data from qry_EDW_NBS, specifically columns Accepts, Rejects, Accept_Impact, etc.    So depending upon the date range they select, a site might or might not have calls answered and if the Calls Answered is zero (nor nothing), I don't want them to be included in the table (the entire row), but I am not sure how to apply the date, or filter the row for both tables, if the column we are checking is only included in one, but not both.  

Power BI Tab

[AndyMills]

Thank you for the suggestion.  I will give this a try and see, but to answer your questions:  

1)  I take it you don't have a common field between both tables for CallID, right? 

     Unfortunately no, there is no common ID field between the two tables.  Would be so much easier if there was as I could easily

     combine the two into one query.  

2).  I also take it the visual has the Site as a filter (based on the image)?

      No, there is no site filter / slicer.  

[PaulDBrown]

@AndyMills 

I take it you don't have a common field between both tables for CallID, right?

I also take it the visual has the Site as a filter (based on the image)?

something you might try is:

1) create a measure to filter the visual

visual filter = COUNTROWS((qry_CMS)

2) select the visual, and in the filter pane, add the above measure [visual filter] to the section "Filters for this visual". Set the result for "is greater or equal"  to "1".

see if that works.

[AndyMills]

Thank you for the suggestion, but that doesn't seem to have worked.  Below is a screen shot of the metric I created inside the qry_CMS table, the date slicer I picked but it is still showing for 0 calls answered

 

[PaulDBrown]

@AndyMills 

When I mentioned Site as a filter I actually wasn't referring to a slicer but in the actual matrix/table (filter context). Apologies for the vague question. 

it would be easier if we could play around with sample data 

however, here is another try:

include the measure for [Calls Answered] in the "filters for this visual" in the filter pane and set the filter value to greater than 0

[AndyMills]

I am trying to mark this as the solution, but for some reason it isn't letting me, but this was the solution I needed and wanted to thank you for the help

[PaulDBrown]

@AndyMills 

That's weird (not letting you mark as solution). Might be worth trying again, or let us know which solution worked and we'll mark it as a solution for you. Thanks!