Cross-filtering not showing all data

[Ismar]

Hello,

this is my issue. I have a table visual with budget and actual figures. As additional 2 visuals, I have budget and actual distributed by months.

So, what happens is this. When I select a couple of months in budget visual, table is properly filtered and it shows correct budget total for those months. When I do the same with actual visual, a table is again correctly filtered, and it shows actual only for those selected months. But, when I filter by both, selecting same months in budget and actual visuals, table gets filtered in a way that it shows budget figures only for those raws having a result in actual. So a total of budget is less than it should be. It does not show the categories where I have budgeted values, without actual. I switched on "Show items with no data", but no effect.

My budgeted values are coming from an excel table as a source, and actual is coming directly from SQL. So, this combination oftenly does not retrieve proper results, like in this case.

I hope I explained this in an understandable way.

Thanks in advance!

[vojtechsima]

Hi, @Ismar 
Do you have Date Table that would link each table on its own by One-Many relationship? (One = Date Table).
If not, I suggest you first create one and then try it again.

[Ismar]

Hello, I didn't have that. I created it now, but no luck, same result. I didn't manage to create active single-to-many relationship with both tables though. Only one can be active. And which ever it is, it filters the same.

[vojtechsima]

Hello, @Ismar 
Make sure it looks like this and the Date Table is marked as "Date Table".

[Ismar]

Well, it looks like that, except I cannot make them both active. My budget table contains column "month" where the fields are simple text (jan, feb, mar...). And actual table, the one from SQL, is where I renamed a month formula derived from actual date (getting 1, 2, 3...) to the same string of months as I have in my bdg table... (jan, feb, mar...). So in the date table, I also put the same values (jan, feb, mar...) trying to connect these month columns... I probably made a mess with that one, but not sure how to do it differently.

[vojtechsima]

Hi, @Ismar 
you can have Data Table that has columns that either have Month as Text or Month as Number.
You then map the tables accordingly, make sure you map Date Table with Your Excel table and SQL table, don't map SQL and Excel together.

Could you share please how your schema looks like, please?

[Ismar]

The thing is, I already have excel and SQL directly connected through some other field (category) as a many-to-many relationship. That might be the problem. When I try to make both active through date table, I get warned about ambiguity.

[vojtechsima]

Can you share your model, please?

[Ismar]

[vojtechsima]

Do you have to create connection between those two when you will create a mutual connection to Date Table?

Also many to many relationship  is never the best option or Both cross filters.

Try to recreate your model and add more dimensional tables, so you have ideally one to many relationship.