Building Custom Keys for Data Model

[hasnain0204]

Hi guys, I have a fact table and based on that table I made 3 other dimension tables. My data does not have Keys in it so I made custom keys through the index option. I removed duplicates before I did this and merged the dimension tables based on the right columns to the fact table. This made me get those unique keys into the fact table to build relationships. In my model view, I have many-to-one (fact to Dimension) relationship but the issue is when I try to filter anything or add a table based on those different dimensions it doesnt work. The table turns blank and the filters dont work. What could be a possible issue here?

[vojtechsima]

Hi, @hasnain0204 ,

I would check that you did use correct column to create the connection (maybe it's silly but that's usually the issue). Then you can check that you have the same data types of those columns and that you have actually some matching values and you use Dimension for the filtering.

[hasnain0204]

So, as an example I will take the addresses dimension which has ZIP(int) and address(text). I used the same columns with the same type to merge then. The merge worked fine and its giving me the right values in the Fact table. I also did the same for Market, region, submarket (text) and got the values but they dont seem to work on the reports page.

[vojtechsima]

@hasnain0204  any chance you could send some sample data or sample file (via onedrive)?

[hasnain0204]

Yeah, give me a minute. One more thing, the dimension table does filter out the fact table. Such as, if i want to filter market through dimension table, it does filter it for the fact table but not for other dimension tables.

[vojtechsima · Accepted solution]

Hello, @hasnain0204 , the dimension part, yes, that's kinda the nature of it when you don't use Both cross filter function, which you shouldn'T really.

You can have workaround for that, that you create measure that checks what is filtered in the Fact table and then you apply this measure as filter for your dimension slicers.