Forum Discussion
Helper IIYou can't create a relationship between these two columns because one of the columns must have uniqu
Hello, don075 ,
the issuse is that you don't have any match, because you have different formatting:
Trns:BridgeTable
Crumb
if I create temporary one, it works just fine:
Super Userhello don075 ,
not sure if this will help, but is it possible to create custom unique id using CONCATENATE()?
for example:
in 'uid,f1,f2' table, concatenate all of them to became u1112000 (for the first line).
this might be usable as unique id.
Thank you.
Helper IIThanks for the response.
I dropped the Users table for now. Trying to link only the Trns and Crumble tables.
In my SQL server, I modified both Trns and Crumble tables and added sKey column. This contains concatenated fields of f1+f2+f3+f4+syear+smonth
Created a BridgeTable witk sKey that contains Distinct f1+f2+f3+f4+syear+smonth
Linked BridgeTable and Trns with sKey
Linked BridgeTable and Crumb with sKey
Added a slicer and added sMonth from Trns.
Added Matrix visual and added fields from Trns
Added Table visual and added fields from Crumb.
When I select month from slicer, it refreshes only the Matrix visual. Table visual remains.
Modified the slicer, removed sMonth and added sMonth from BridgeTable.
Now the slicer shows the month but it added a blank entry at the top. However, the BridgeTable doesn't have any blank values.
When the Blank value is selected, it refreshes both Matrix and Table visual with some data. Cannot figure out why only some records are displayed.
Any other month is selected, both visual become empty.
I am not sure what's wrong. Apologies for the questions as I am new to PowerBI.
Including a screen shot with the dashboard.
When selected blank
When selected Month 1
- vojtechsima
don075 hello,
so probably what you can do, create 4 dimensions f1->f4 with unique values, then connect them respectively with your fact table.
Then create calendar (table with dates worth of at least one year without gaps), create helping columns such as year or month and then connect the year and month you have in your fact table to the Calendar.
Then create slicers from the dimensions and you can operate everything through dimensions.
- Irwan
hello don075
i am not sure but if there is no measure perhaps that is a relationship problem since it looks like to be connected through blank value.
i believe the smonth in slicer should be taken from table with relationship with data in table visual.
if possible, please share your sample pbix with removing any confidential information.
Thank you.
- don075
I have attached the report. recreated the report with ImportData. My original report was created with DirectQuery. This thread won't let me upload the file. I am including the google share link to the file.
https://drive.google.com/file/d/163e3hhTqZaL-7Z-BfbRFC8N_hPNxdSJ5/view?usp=sharing
- vojtechsima
Hello, don075 ,
the issuse is that you don't have any match, because you have different formatting:
Trns:BridgeTable
Crumb
if I create temporary one, it works just fine:
ajohnso2Solution Supplier
If you drop your user table and leave your 2 fact tables remaining why not join those 2 together in your database (or SQL, or in power query) to save all this messing about. But as someone has already stated, you do not have any unique keys to join users and fact. Is there a reason you cannot have a user key in your fact tables?
FYI - from info above the reason your slicer is not the behaviour you want is because you have used sMonth from 1 fact table (the other fact table has no relation with the other), your slicer should be using a value from your bridge table if you want it to filter both fact tables.
- don075
Thanks for the reply. I cannot link this in my database as there are duplicates on both tables. I thought maybe powerbi will let me view crumb data when clicked on Trns table, even with duplicates.
I have been developing reports with Crystal reports for years and doing this there is so simple, even with duplicates. But the users want this done in powerbi.