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.
@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.
Did I answer your question? Mark my post as a solution.
Proud to be a Datanaut! Appreciate your Kudos Feel free to email me with any of your BI needs.
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?
@nsrshkh1 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.