Switch date columns of matrix based on selected slicer value

[timalbers]

Hi all,

I really appreciate anybody who can help me on the following issue.

I currently have two matrix visualizations of the same fact data. Both show the number of products in each product category per date. The only difference is that in the first matrix "date" refers to the date of invoice wehreas the second matrix uses the date of delivery. These two fields are different date columns in the same data table.

My problem is that I need to merge this information into one table only, where the user is able to choose between showing the data based on the date of invoice OR the date of delivery. So there will be a simple slicer filter where one can switch between one and the other option. Depending on which option is selected there should be either the date of invoice column or the date of delivery column used as column parameter of the matrix visualization.

Left shows parameters of upper table, right lower table

I know that there is a workaround by using two tables which are shown depending on a bookmark selection. Unfortunatly my further report design does not allow a solution where there are two tables used. It has to be one table only. Otherwise the logic of my other visuals doesn't make sense anymore.

Basically I'm trying to build something like a SWITCH() function but with using whole columns instead of measures as parameters.

Thank you very much for any suggested approach!

[DataInsights · Accepted solution]

@timalbers,

One approach is to use field parameters. You'll need two measures, one for Invoice Date and one for Delivery Date. When you add these two measures to a field parameter, you have the option to create a slicer. This slicer will enable users to toggle between the two measures, allowing them to see either Invoice Date or Delivery Date.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

Are you familiar with the USERELATIONSHIP function? It allows you to use an inactive relationship within a measure. Let's say the active relationship between the date table and fact table is based on Invoice Date. This is a straightforward measure that uses COUNT. The other measure which is based on Delivery Date requires USERELATIONSHIP to activate the inactive relationship based on Delivery Date. It will look something like this:

Count by Delivery Date =
CALCULATE (
    COUNT ( FactTable[Product] ),
    USERELATIONSHIP ( FactTable[Delivery Date], DimDate[Date] )
)

You could also use a calculation group, but field parameters are simpler to create.

[timalbers]

@DataInsights thank you very much for your fast and well explained approach!

I did not know the possibility to create field parameters yet, thats an awesome new feature and I see how it can help me in my scenario.

Problem is the USERELATIONSHIP function. When I create an inactive or active relationships between my fact table and my date dimension table, the date hierarchy of delivey date and invoice date gets lost because the column of the fact table are on the n side of the n:1 relationship. I need the hierarchy for the drillthrough. 

So currently even without the possibility to switch between the two date columns, my two visuals do not access my date dimension table. It simply uses either the delivery date column or the invoice date column of the fact table. The fact table is connected to the date dimension via a third column called "order date", where I do not need a hierarchy.

[DataInsights]

@timalbers,

This brings up an important principle: visuals should use fields in dimension tables. You can create a hierarchy in your date table, and use this hierarchy in visuals. A hierarchy is simply a grouping of fields such as Year and Quarter. There's a default date hierarchy for each date field in a model, but this can be disabled in Settings. When you mark your date table as a date table, these default hierarchies are removed from your date table.

[timalbers]

Indeed this was the key to the solution. I fixed my date logic and finally made it work just as I want it to have. Thank you very much for all your help! I really appreciate it!

[MasTristan]

Hi I am on an issue very similar to yours,

I just set up a field parameter taking my two date fields, but then I lose the date hierarchy. The problem is I need to display by year&month on my matrix. 

Did you manage to do this using field parameter ? 

Thank you

[timalbers]

Hi MasTristan

I don't know your exact szenario but my problem was solved creating field parameters on the two measures which count values in my fact table - not on the date fields of the fact table.

More detailed: I created two measures. Both are counting the values of the same column in my fact table. The only difference is the relationships to my date table they are using to execute that count. One uses the invoice date field, the other the delivery date field. How to create such measures just look a few messages earlier in this feed.

Those two measures build my field parameter. Not the date columns themselves.

In my matrix I put this field parameter into the value section.
In the column section remains the standard date column of the date table, where the date hierarchy is just fine.

Like @DataInsights taught me before, I strongly propose to always use the date column of your date table in visuals and not date fields of the fact table.

Hope this helps!

If there are any further questions, dont't hesitate to ask.

Sincerely

Tim

[DataInsights]

Glad to hear that!