cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MS_P
Helper I
Helper I

Filter not working as expected

Hi,

 

I created a new table using summarizecolumns() and both the new table and source table have a column "Type". I have a dropdown filter for Type on the page which is not impacting the values of the new table. I tried creating a relationship but it said error due to circular dependency. I want to be able to filter out values based on "Type".

 

Any help would be appreciated.

13 REPLIES 13
amitchandak
Super User IV
Super User IV

@MS_P , First of all, slicer values are not taken by new table.

 

Share the table script to check. Try to use summarize in place summarizecolumns and check if join works



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi, thanks for your response. Can you please elaborate on why slicer values don't affect the new table? Also the DAX Query for creating the new table is:

Table2 = SUMMARIZECOLUMNS(Table1[Type],Table1[Date].[Year],Table1[Date].[MonthNo],"Number of rows", COUNT(Table1[ID]))

v-kelly-msft
Community Support
Community Support

Hi  @MS_P ,

 

As tested here,it works fine,no error returns.

v-kelly-msft_0-1620372370256.png

 

v-kelly-msft_1-1620372385380.png

v-kelly-msft_2-1620372396097.png

Could you pls share your .pbix file for test?Remember to remove the confidential information.

For my .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Thanks for your response, but I can't share my PBIX file. But the issue is that in my file a one to one relationship between "Type" is not being accepted as a valid one and others have a circular dependency issue. And I have a date and type filter (from table 1) on the page which aren't working if they're not having an active relationship between the types or dates. 

v-kelly-msft
Community Support
Community Support

Hi  @MS_P ,

 

Could you pls show some sample data with expected output?

 


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Hi @v-kelly-msft

 

I can only attach the screenshots since I don't have an attach file option available. The slicers for type and date are from original table and the graph needs to be filtered by both the slicers which is not happening. The only relationship allowed between "Type" of the tables is many to many and then the graph does not get filtered by the date slicer. I tried creating columns in both the tables to have matchable dates - monthly 1st date in original date and a combinevalue column in summarized table and then create a relationship which is not accepted.

 

Graph and filters.pngModel.png

v-kelly-msft
Community Support
Community Support

Hi  @MS_P ,

 

Is your issue solved?Does my suggestion work ?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Apologies for the delay in response, but a one to one relationship is not being allowed between "Type" and thus am not able to resolve it using this, thanks for your response.

v-kelly-msft
Community Support
Community Support

Hi  @MS_P ,

 

No,what I mean is to create a dimensional table between the 2 tables then create a one to one relationship as my sample show it.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Hi @v-kelly-msft,

As I mentioned before, a one to one relationship is not being allowed between the "Type" dimension table and the other Table1 and Table2.

one to one not allowed.png

v-kelly-msft
Community Support
Community Support

Hi @MS_P ,

 

Relationship is not the key factor,could you pls provide some sample data with expected output for test?

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

Hi @v-kelly-msft,

What essentially happens is this:
There is a table "A" which consists of id, type, date, a few questions.
Another table "S" consists of id, type, date (month and year format) with the number of types per month.
I need to plot a graph for number of A per month and S per month.
But A doesn't directly have number of rows per month, for which I use SUMMARIZECOLUMNS to achieve the same (say R).
The page has filters from A (type and date) which need to be able to filter out the graph consisting S and R.
A isn't filtering out R itself even with a many to many relationship between types.
Attaching screenshots for A and R.

Original dataOriginal dataSummarized dataSummarized data

v-kelly-msft
Community Support
Community Support

Hi @MS_P ,

 

If so,create a dimensional table for "Type":

dim table = VALUES('Original table'[Type])

Then create relationships as below among the three tables:

v-kelly-msft_0-1620874077474.png

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors