DAX - USERELATIONSHIP for slicers

[emilypoon]

Hi,

I would like to get a help on setting up a USERELATIONSHIP function to activate a slicer.

Here is my data model with 3 records only (Table name = QuantityTable).

The table contains 4 columns with 2 columns are date-related -> OrderDate and DeliveryDate

Then, I set up a MasterCalendar table with the CALENDAR function.

Now I have 2 tables in my model -> QuantityTable and MasterCalendar.

I linked up the relationship by connecting "Date" from the MasterCalendar table with the "OrderDate" & "DeliveryDate" column from the QuantityTable. The solid line represents an active relationship and I set it as the "OrderDate"

I created DAX formula with USERELATIONSHIP to calculate the "Quantity by Order Date" and "Quantity by Delivery Date". The formula works perfectly in a card visual or line/bar visuals etc.

In the below example, if I choose Month=3 from the MasterCalendar slicer, it gives me the below result with "Blank" by OrderDate and "10" by DeliveryDate which is perfect.

Now, I want to create a new slicer with "Item" as follow.

I want the "Item" slicer to dynamically displays the available fruit names based on "DeliveryDate".

For examplem, I only want the "Item" slicer to display "Apple" if I choose Month=3 based on "DeliveryDate".

I know the active relationship falls to "OrderDate" as per the model setup, but now I want to activate "DeliveryDate" in here instead of "OrderDate".

If I choose Month=3 now, the "Item" slicer will become blank because this slicer only recognize the active relationship of "OrderDate" but not the "DeliveryDate".

As you can see in the below screen shot, the card visual of "Quantity by Delivery Date" is 10 if the month is March, while everything in the "Item" filter is disappeared because it recognizes "DeliveryDate" of March has no record.

I tried to google around to see how I can activate an "inactive relationship" for a slicer but not able to solve this problem. I know I can use USERELATIONSHIP to activate an "inactive relationship" but that is only workable for other non-slicer visuals. How can I do the same for a slicer so that the "Item" slicer would be able to display based on "DeliveryDate"?

Appreciate if anyone can suggest anything that is workable for the above scenario.

Thank you so much!

Best regards,

Emily

[Anonymous]

I resolved this by placing the following measure in the "Filters on this Visual" on the slicer

ApplyToFilterVisual = if(Quantity[Quantity by Delivery Date]>0, [Quantity by Delivery Date], Quantity[Quantity by Order Date])

The value on filter is set to greater than zero

The Slicer is the Item[Items] and the relationship cardinality is Single

 

[parry2k]

@emilypoon  First of all great job on explaining the problem. Well done!!

Unfortunately, I don't think there is any solution available at this time the way your model is setup. You should have sepereate table for items which should have relatoinship with your quantity table and then slicer should use Item from new items table.

It will show all the Items  regardless which month you use. This will be as close as you can get at this point. Instead of showing no item in the slicer, it will show you all the items.

[emilypoon]

Thanks for your reply @parry2k !

I can create a new table only for "Item" and make it as a slicer, but it will show the same wrong result because I cannot remove the relationship with the MasterCalendar table.

Here is what I did - create a new Table called "ItemTable" with "ItemName" column.

Link the ItemTable with the QuantityTable with relationship.

In the report page, add a new slicer for "ItemName" from the ItemTable (see the PINK slicer).

If I filter Month=3, you can see that everything from the "ItemName" slicer is still disappeared because it is still recognizing the "OrderDate" as the active relationship instead of "DeliveryDate".

In this case, how can I activate the "DeliveryDate" field if I create a seperate slicer for "ItemName"?

I can't remove the relationship between MasterCalendar and QuantityTable because I really need that in my real case. Do you know any other DAX formulas that can be used to activate the "ItemName" slicer dynamically based on the date column that I want?

[parry2k]

@emilypoon change your cross filter direction to single between item and quantity table. Double click on relationship and that is where you you change the direction from both to single.

[Anonymous]

Hi Emily,

Thanks for detailed explaination.

Here is the alternate methodSolution

Let me know if this works!

[emilypoon]

@Anonymous Thank you for proposing a great solution with demo file too!! Appreciated a lot!

I just tested your solution in my dummy file and it works perfectly (with the support of Power Query too to transform the ItemTable to your format), so I also tested the same in my real company files and it also worked well.

However, the limitation here is that the "Item" slicer will always display all the options and cannot dynamically display only the relevant items given for a specific date (ie. Apple / Orange / Cherry / Banana will always show in the slicer regardless to OrderDate or DeliveryDate.

In my real product, I will have 8 sets of different dates with over 10 columns for "items" which is far more complicated than my dummy example.

Is there a way to only display the relevant items in the filter instead of showing all of them?

In addition, I created a separate model in Power Query for data transformation and I am not sure if this will affect the performance and speed if we have millions of data record to be filtered by the users.

[Anonymous]

Hi emilipoon, Thanks for your feedback! I think data is displaying correctly.

We are filtering on month, in my file I made same month order date & delivery date i.e. June2019.

That is why when you select June they show both Cherry and Orange. If there are no order with same order/delivery month then items will be reflected according to relevant date type.

Please show exactly the problem you are facing with pics to understand in better.

Thanks

NSRSHKH

[emilypoon]

@Anonymous Thanks for your feeback and sorry for my late reply.

Your proposed solution is a workaround that works for the slicer only, but in my real case I will have over 20 charts linking to each slicer.

Even if the data can be displayed properly in the slicer by transforming the data into a separate table, but that won't give the correct result if I also have chart visuals.

Here I am adding 2 charts in my example.

If I choose Month=3, the top chart of couse will be blank because it is based on "OrderDate" as the active relationship and there is no March data based on OrderDate.

In the bottom chart, it displays the bar for "Apple" if Month=3.

However, if I unfilter all the slicers, the bottom chart will always display 2 bars based on the below settings as I put both "Item" and "Quantity" column from the QuantityTableTranform.

The problem is that I cannot choose "Quantity" column from the original "QuantityTable" because the chart will still be a blank due to active relationship of "OrderDate"

In view of the above problems, do you have any other alternatives in order to show all the items in the slicer and the chart simultaneously given that we have multiple "dates" column?

Best regards,

Emily

[Anonymous]

@emilypoon 

I am still not getting your requirement.

1) If you select Month=3, you want to display the "Apple" in Item slicer and Chart.

2) If no Month slicer item is selected then all fruits will be displayed in Item slicer and chart based on Order Date will be displayed.

Is this what you need? If yes then

Total Quantity By Order Date = CALCULATE(SUM(QuantityTable2[Quantity]),QuantityTable2[DateType] ="OrderDate")

Total Quantity By Delivery Date = CALCULATE(SUM(QuantityTable2[Quantity]),QuantityTable2[DateType] ="DeliveryDate")

TryThisMeasure = IF(ISFILTERED('Date'[Month]),SUM(QuantityTable2[Quantity]),[Total Quantity By Order Date])

Use "TryThisMeasure" instead of Quantity in Value section of Chart Visual.

Hope this resolves your issue. If yes then please mark this as Solution. Thanks

[emilypoon]

@Anonymous  Thank you for your reply and proposed solution.

I understand your workarounds but this is limited in certain situations, especially my real project contains over 20+ slicers similar to "items" and over 10+ "date related" columns that need to be displayed dynamically in both of the slicers and charts.

I tried to search from PBI community and here below posts is a reference case which is exactly the same problem to what I am currently facing:

https://community.powerbi.com/t5/Desktop/Slicer-Filters-reflect-quot-Active-quot-data-relationship-when/td-p/423632

Do you have any other ways to solve this problem?

Sorry to borther you again and I really appreciated your great help on this, thanks!

[Anonymous]

@emilypoon 

Solutions are provided based on the query.

I saw the other query you provided link for and that way is not possible in current data arrangement.

However you can modify your current DAX to suit my solution.

I might guide you however I would need entire sample data with visuals arrangements to see the effect of one on other.

Thanks, meanwhile if found other solution I will post here.

[emilypoon]

@parry2k  I just tried as per your proposal but I got the below error message and it stops me to change the cross filter direction to single. How can I fix this error?

Thanks for your help!