Forum Discussion
DAX - USERELATIONSHIP for slicers
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
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:
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
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.