Forum Discussion
DAX - USERELATIONSHIP for slicers
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
12 Replies
Anonymous 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.
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?
Anonymous 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.
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 zeroThe Slicer is the Item[Items] and the relationship cardinality is Single
