cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emilypoon Regular Visitor
Regular Visitor

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

image.png

 

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

image.png

 

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"

image.png

 

 

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.

image.png

 

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

image.png

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.

image.png

 

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

 

 

 

 

11 REPLIES 11
Highlighted
Super User
Super User

Re: DAX - USERELATIONSHIP for slicers

@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 Smiley Happy
Feel free to email me with any of your BI needs.





emilypoon Regular Visitor
Regular Visitor

Re: DAX - USERELATIONSHIP for slicers

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.

image.png

 

Link the ItemTable with the QuantityTable with relationship.

image.png

 

 

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

image.png

 

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".

image.png

 

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?

 

 

Super User
Super User

Re: DAX - USERELATIONSHIP for slicers

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





emilypoon Regular Visitor
Regular Visitor

Re: DAX - USERELATIONSHIP for slicers

@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!

 

image.png

 

 

nsrshkh1 Member
Member

Re: DAX - USERELATIONSHIP for slicers

Hi Emily,

 

Thanks for detailed explaination.

 

Here is the alternate methodSolution

 

Let me know if this works!

emilypoon Regular Visitor
Regular Visitor

Re: DAX - USERELATIONSHIP for slicers

@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.

 

nsrshkh1 Member
Member

Re: DAX - USERELATIONSHIP for slicers

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 Regular Visitor
Regular Visitor

Re: DAX - USERELATIONSHIP for slicers

@nsrshkh1 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.

image.png

 

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.

image.png

 

 

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.

image.png

 

 

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"

 

image.png

 

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

 

nsrshkh1 Member
Member

Re: DAX - USERELATIONSHIP for slicers

@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.

 

Scenario1.pngScenario2.png

 

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

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 106 members 1,694 guests
Please welcome our newest community members: