Want to show data from two tables

[AshDil]

Hi,

I have two tables as follows:

Table-1:

CATEGORY   ID        SALES

A	101	150
B	102	230
C	103	400
D	104	780
A	105	300
B	106	200
C	107	100
D	108	150
E	109	200
F	110	200

TABLE-2:

CATEGORY      ID    VALUE

A	101	100
B	102	200
C	103	300
D	104	290
E	105	400
A	106	200
B	107	100
C	108	150
D	109	100

Now I need to add Category and ID to slicers, based on slicer selction want to show data but the result is not showing as required

Please help me to do.

Thanks,

AshDil

[Pragati11]

HI @AshDil ,

Can you add some details on the data model on these 2 tables? what relationships they have?

Thanks,

Pragati

[AshDil]

Hi @Pragati11 ,

The both tables have many to many relationship. 

Thanks,

AshDil

[Pragati11]

HI @AshDil ,

I think your many-to-many relationship might be working weirdly.

Try intriducing a bridge table between your two tables and create a many-to-one or one-to-many relationship of these 2 tables with this bridge table.

Bridge table will be a table having distinct values of the column that is currently used to create a relationship between your tables.

Check an existing thread on creating bridge table: https://community.powerbi.com/t5/Desktop/Bridge-Table-to-bypass-Many-to-Many-Problem/m-p/943389

Thanks,

Pragati

[AshDil]

Hi @Pragati11,

I have tried creating dimtable having Category and ID (as I need to use these 2 fields in slicer) to act as bridge between table-1 and table-2. Tried creating relationship again facing many:many relationship problem.

Thanks,

AshDil

[Pragati11]

Hi @AshDil ,

Can you share some kind of screenshot on what you have as a relationship?

Thanks,

Pragati

[AshDil]

Hi @Pragati11 ,

Please find following screenshot. I have tried to create relationship on ID.

Thanks,

AshDil

[Pragati11]

Hi @AshDil ,

Another question. Whay is the high-level column here - Category or ID?

I am trying to understand why relationship is on ID column, why not on Category column.

Also if you want relationship on ID column, then have a bridge table with Distinct ID column values. No point in creating an additional bridge table if you still end-up with many-to-many relationship.

Thanks,

Pragati

[AshDil]

Hi @Pragati11 ,

High level column is Category. I am creating relationship based on ID because they are unique. But the problem in ending up in many:many relationship is some ID's are present in 2 or 3 categories (eg: 101 is present under Category A,B and D).

Please suggest me if any other way to do it.

Thanks,

AshDil.

[Pragati11]

Hi @AshDil ,

I think I mentioned the way in my previous response itself.

You will have to create a bridge table with distinct values of ID and then create a relationship with other 2 tables.

In your slicer on report page, use ID from this bridge table for filtering. It should work.

In case this doesn't work, then do share a sample pbix file by removing any sensitive data from the pbix file. You can do this by uploading it to Dropbox or you can use WeTransfer as well.

Thanks,

Pragati

[Greg_Deckler]

@AshDil You need to create a relationship between the tables.

[AshDil]

Hi @Greg_Deckler ,

I have tried creating relationship between both tables, both having many to many relationship so the values are duplicating because of it.

And I tried creating dimtable having column Category and ID, again relationship between dimtable and table-1 is many:many an also between dimtable and table-2 is many:may.

Thansk,

AshDil