Data not filtering on related table

[CF25]

Hi All, 

Never posted a question on Power BI Community but I'm feeling really stuck. 

I have two fact tables and 2 dimension tables. 

My fact Tables: Marketing Costs & All Orders. 

When  I create visualiztions based on Channel (the field the tables are related by) the data breaks it down properly. BUT when I create a visualization based on Month I get the total of all months of marketing costs for every month. The only way it shows me a proper breakdown is by channel. 

Can someone please direct me on what I'm doing wrong!! 

I have tried many changes, having already reconstructed my data model into something more similar to a star schema... 

[Pragati11]

Hi @CF25 ,

In which table does this MONTH column reside? Is it in fact table only?

Thanks,

Pragati

[CF25]

Hi @Pragati11 , the month column is from a date field in the orders table but there is a date field in the marketing costs table as well. 

[Pragati11]

Hi @CF25 ,

I suggest you introduce a CALENDAR table in your data model. Connect this table to both of your tables - Orders and Marketing Costs.

Then use Month column from Calendar table and metrics from other 2 tables on your report for visualisation.

The reason for using a CALENDAR table is it will avoid any data in-consistencies in other 2 tables like there could be data for a certain day/month in ORDERS table but not in Marketing table.

Try this approach and see if this works.

As part of best practices one should have a CALENDAR date table in Power BI for such reporting purposes.

You can refer following article on creating Calendar table:

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Thanks,

Pragati

[CF25]

@Pragati11 , thanks for your reply. I actually had tried that already. Power BI wont allow me to connect the tables by the column Channel AND through another table by date. I tried just having the calendar table connect to order table and to marketing costs table. Then it would show a proper breakdown of the months but an incorrect breakdown of the channel. 

Any insight on this?

[Pragati11]

Hi @CF25 ,

Are you looking for 2 different breakdowns?

1. By Channel
2. By Month

If yes, then may be create summarised measures at Month and then at Channel level; then further use them in your report.
If it is possible share some sample data or your pbix file by removing any sensitive information from them.

Thanks,

Pragati

[CF25 · Accepted solution]

@Pragati11 I found a solution. I restructured my data and now everything is pulling correctly. See attached image.

I really appreciate your help with this!

[CF25]

When I created a calculated column of summarising costs by Month - it would not show up at all on the chart.

[CF25]

Really appreciate your help with this @Pragati11 !

Yes, for now I want the 2 different breakdowns...but I would imagine there will be other ways I'd like to play around with it. 

I'm going to try those measures and see how it works. 

For now see attached 

file:///C:/Users/feigelstein/Downloads/marketingcosts%20-%20sample.htm 

file:///C:/Users/feigelstein/Downloads/orderdata%20-%20sample.htm