Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 col...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?

 

 

parry2k's avatar
parry2k
Super User
7 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Emily,

     

    Thanks for detailed explaination.

     

    Here is the alternate methodSolution

     

    Let me know if this works!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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