Slicer for two Fact tables - SUMMARISECOLUMNS?

DAX DAX Calculations
1

Hi,

I have two tables which contain targets for different business areas (HardwareTargets & SoftwareTargets). They differ in content and shape and cannot be appended together. What is common between the two is that they contain a [Year] column (eg 2018, 2019) and a [BudgetType column (eg Budget, 3+9 Forecast, 6+6 Forecast, 9+3 Forecast).

To allow the user to filter both of the tables by BudgetType, I created a table in Dax, containing the unique values of [BudgetType] from both tables:

EVALUATE
(
DISTINCT (
UNION (
SUMMARIZE ( 'HardwareTargets ', 'HardwareTargets '[BudgetType] ),
SUMMARIZE ( ‘SoftwareTargets’, ‘SoftwareTargets’[BudgetType] )
)
)
)

Returns:

Budget
Forecast 3+9
Forecast 6+6
Forecast 9+3

I then relate this new lookup table to my two budget tables and the user can happily select one of the four values in their slicer and it filters both budget tables.

The problem is that when the user slices on Year, say 2019, we still get all the values, whereas only the below are actually present in the tables for 2019:

Budget
Forecast 3+9

I therefore need to filter my DAX table so that it takes into consideration the currently selected Year value. I tried to use SUMMARISECOLUMNS with a filter of selected value, but it does not return any data?

EVALUATE
VAR __YEAR =
SELECTEDVALUE ( Dates[Year] )
RETURN
(
DISTINCT (
UNION (
SUMMARIZECOLUMNS (
‘HardwareTargets’[BudgetType],
FILTER ( ‘Vehicle Targets’, __YEAR )
),
SUMMARIZECOLUMNS (
'SoftwareTargets '[TargetType],
FILTER ( ‘Vehicle Targets’, __YEAR )
)
)
)
)

Yet, if I hard code my variable __YEAR to 2019 it does work as I want?

Am I going about this the wrong way?

Many thanks in advance for any help - first time using the forum here!

Mark

PS: How do I retain the formatting of the DAX code in my posts?

2

@Mark
Any chance you can post a some sample data? Seems like it could be a relationship issue, but would like to see the actual tables and such

Nick

Enterprise%20DNA%20Expert%20-%20Small

3

Hi Nick,

Sure, I have upload a simplified example of my report to Dropbox, hopefully you are able to download?

Currently when you slice on either 2018 or 2019, the BudgetType slicer offers all 4 Budget Types, however only two are valid for 2019: Budget & Forecast 3+9

The BudgetType slicer comes from my Dax table _BudgetTypes

If you look at this code, I have commented out the Filter part of SUMMARIZECOLUMNS which I had hoped would fix my issue, as the values being returned by the would only relate to the selected year.

My model also uses a Dax table _YearMonthBridge, which I created to address the issue of different different granularity between my fact tables; the Budget tables are at Year/Month level, whereas the Sales tables are at date level. I am sure this is not the best approach and I have tried to understand the use of TREATAS which I think from Sam’s videos is a solution to this issue, but I am my yet to grasp it fully. Any help in this area would also be very appreciated!!

Thanks

Mark

4

I’ve had a look at this one.

You don’t need this below. It over complicates things.

image
image.png1166×500 28.1 KB

Have you reviewed some of the budgeting videos I’ve made about solving this?

You can do this with TREATAS instead. It’s much cleaner and can improve the simplicity of your model immensely.

You can just create the YearMonth column in the Date table instead and use this as a reference dimension to filter the budget/target details.

image

I personally prefer Month & Year and is what I have in all my date tables you’ll find when you download any resource.

I’m really not sure what you are attempting with the SUMMARIZECOLUMNS from what I can see in the model.

Check out these videos below. This can be way simpler than you think.

5

Hi,

Thanks for your input - I’ve come across your videos on TREATAS with budgeting before and have been meaning to give it a go since! I’ve now updated my sample report to use TREATAS and can definitely see the benefits it brings.

However, my initial query still applies. How do I slice two fact tables with a single slicer, which will only offer the available / currently filtered values in both tables?

To recap, when the user selects 2019 in the Year slicer, the Budget Type slicer should only offer ‘Budget’ and Forecast 3+9. If I have two Budget Type slicers (one pointing to BudgetType in each of the fact tables), then this will work. However I only want the one slicer - hence building the lookup table _BudgetTypes.

I was originally querying why my SUMMARIZECOLUMNS Dax table was not dynamically changing using SELECTEDVALUE of Year, but have since found out that DAX tables are not dynamic and won’t change based on user input.

I have found a solution, but I am not sure it is ideal - please see attached v2 of my sample report.

I have removed the YearMonth bridging table and the Budget tables now standalone from the sales fact tables and I am using TREATAS to pull through the Budgets. However I still have my SUMMARIZECOLUMNS lookup table _BudgetTypes, which now additionally joins to the Date table on Year.

This is giving me my desired result, in that when 2019 is selected, the user only sees the two relevant Budgets in the BudgetType slicer and this one slicer filters both Budget tables.

Thoughts appreciated!

Mark

6

Ok looking much better.

Few other changes I would make.

You don’t want relationship like this. It can cause many issues. Always think ‘waterfall of filters’, this is what you want to aim for at all times

image
image.png1084×553 26.3 KB

You want instead to have year as a lookup table above these current lookup tables.

Something like this.

image

image
image.png903×416 17.3 KB

Then when slicing by year, always remember to slice from the top level so that the filter flow down like a waterfall.

1 Like
7

Hi,

Great, thanks again for the tips. The bi-directional filters on the date to sales tables wouldn’t normally be there, it was just that my sample table only had one row per date, thereby allowing it to default to a one-to-one relationship and therefore by-directional.

However, my initial query does still apply :slight_smile:…

Is there any way I can make my Budget Type slicer only offer the values applicable to the filtered data in my budget tables? So when 2019 is selected, only the following are offered:

  • Budget
  • Forecast 3+9

Many thanks

Mark

8

I think you’ll find the way your model was there wasn’t a way to create the single direction relationships due to a circular dependancy issue.

I’m confused regarding your other question, isn’t it already doing what you need?

image

image

This just is already happening naturally with these filters I would have thought?

image
image.png840×583 17 KB

9

If this isn’t what you need, really think about the filters here and how they flow in your model.

This is what you need to truly understand to get to the right answer.

Think…

When I filter Year, what does that filter in BudgetTypes, then what does that filter in the Targets tables.

10

Hi,

Apologies for the late reply…

I have been back to my model and I had made a mistake, I had forgotten to re-point my Year slicer from the Dates table to the new Year column from the derived table Years. As soon as I made this change, the BudgetType slicer behaved as I required.

I feel much happier with this cleaner model, my better understanding of flow and a slicer that works as the business wants - many thanks for your help!

Mark