Forum Discussion
DAX - USERELATIONSHIP for slicers
Hi Emily,
Thanks for detailed explaination.
Here is the alternate methodSolution
Let me know if this works!
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.
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
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
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