Two Fact Tables with Date Table Filtering, One filter correctly, the other does not.

[Index_Match]

Hi all,

I have a date table and two fact tables set up. I do not have any relationships established between the dates table and the two fact tables. I'm using the Month-Year from the date table to show running revenue over time. 

However, when I select a month on the bar chart (x-axis = Month-Year, y-axis= $), one fact table filters to the corresponding values for that month, yet the other fact table does not filter. I have triple checked the data types to make sure they match the date table and they do.

TLDR: Fact table will not filter when date-table month-year is selected on chart. 

[v-xulin-mstf]

Hi @Index_Match,

You should avoid applying meausre and Date Table Filter to the same visual.
The date filter in measure filters out values, which makes the date filter look like it doesn't work.

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @Index_Match ,

I presume that you have some sort of virtual relationship coded as part of your measure that enables the unrelated fact table to filter based on a selected value of the date table. If you share your measure DAX I should be able to confirm/explain why you are getting the behaviour you currently are.

Is there a specific reason why you're not relating your date table to the fact tables? If not, then you need to do this as this is the most fundamental function of the star-schema.

Pete

[Index_Match]

Hi Pete,

Thank you for the response. The reason I haven't established a relationship is because I want to show the running total between two dates and when I connect the dates to the date table it shrinks the chart to only show those months that has dates in the fact table. 

This is the same DAX measure that I have in both fact tables and I assume that is the virtual relationship that you mentioned. 

Monthly Revenue = CALCULATE(SUM(Table1[Monthly Revenue]),

FILTER(Table1,

Table1[StartDate]<=CALCULATE(MAX(Dates[Date])) &&

Table1[End Date]>=CALCULATE(MIN(Dates[Date]))))

[BA_Pete]

Hi @Index_Match ,

Ok. So it appears as though you're getting the current behaviour due to your references to the date table in your measure (<=CALC(MAX(Dates[Date]...), although pretty sure you figured this out already.

The correct way to implement this, accounting for the issue of only populated dates showing, is as follows:

1) Create relationships to both fact tables on Dates[Date] 1 : MANY fact[Dates].

Then either:

2a) Add '+ 0' to the end of your measures. This will force PBI to show a datapoint of zero value even where there is no value to display.

2b) Right click on one of the dimension values (probably Dates[Date]) in the 'Values' list on the right, and select 'Show items with no data'. This pretty much does what it says on the tin. You may need to apply other filters to your visual to limit the range of dates that it returns, such as 'Number of Transactions > 0' or similar.

Then:

3) Use a dimension from the Dates table for all axes.

Both options will probably require you to rewrite your measure slightly. I'm guessing you'd need to remove the CALCULATE from '<=CALC(MAX(Dates[Date]... ' and use a KEEPFILTERS or something instead, but this will depend on your specific scenario/data/required output.

Pete

[dkaushik]

Hi @Index_Match , 

To have the best experience of slice and dice by year/month/date I would recommend you two make relationaships betweeen your Date table and Fact tables. That way, you will be able to use month-year column from your "Date" table and visualize values from your Fact tables with 100% filtering.

In your case currenttly, the table from where you are using "Month-year" will be able to filter the values coming from that table only not the other Fact table as there are no relationships that binds those two together. For this you will have to make another visual and use Month year from second fact table and also use values from the same table.

Hope I was able to explain it.

Thanks,

Dheeraj
If this post helps, then please consider Accept it as the solution and give thumbs up to help the other members find it more quickly.