Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
5 years ago

Need help on drilldown

Hi guys,

 

Need help on drill down.

I have this tables 

 

One of my visual is analyze the "OrderLog" table based on the Reason and I'm gonna add Drilldown to another page for it. 

At the page for my detail drilldown, I have put the Order table (table visual) as well as the Detail table. The drill down key is set to Reason field.

 

So in my mind is like below:

1. At my 1st visual of ORDERLOG by Reason, it is a bar chart, so each bar for each Reason, I can right click and select Drill down to that detail page.

2. At the Order table, I expected will have a list or ORDERS with the Orderkey that refer the ORDERLOG with the related Reason. 

 

Point 2, understand that it is not possible as the relation is 1:M from ORDERS to ORDERLOG, so obviously ORDERLOG cannot filter ORDERS. And I don't intend to switch the relationship to Both. So what I can do is in my table visual of ORDERS I just add the Reason field, so when it is inner join, it will only listed OrderKey that relate each other.

 

But how about the other table, DETAIL, how to make the DETAIL also filtered based on what OrderKey that already listed in ORDERS table visual. 

So currently I have something like this:

As you can see, in the drill down page, the ORDERS is showing the innerjoin because I add Reason field from ORDERLOG, but the DETAILS is still showing whole records, cannot filtered based on the Orderkey that display for certain number only.

 

What is the best solution for this ?

 

Thanks in advance

Need Help

5 Replies

  • admin_xlsior's avatar
    admin_xlsior
    Post Prodigy
    5 years ago

    I'm wondering if I can use CROSSFILTER function for this.

     

    I tried to create measure like this :

     

    OrderID-log = CALCULATE(
                         DISTINCT('DETAIL'[Orderkey]),
                         CROSSFILTER('DETAIL'[OrderKey], 'ORDERLOG'[OrderKey],Both)
)

     

    However got this error:

    Does it mean I have to create relationhip first between ORDERLOG and DETAIL and make it inactive ? If that so, what is the different by using USERELATIONSHIP ?

     

    Still actually when using both (after setup the relationship and put not active), both are not working, it requires one side to be single value whereas both of my data is Many to Many.

     

    Thanks,

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      5 years ago

      Hi admin_xlsior ,

       

      In addition to the CROSSFILTER function,The DISTINCT function returns a column of unique values,measure cannot get this result directly.

      You can share a sample pbix after removing sensitive data, so that we can do more tests to see if it can help you solve this problem.

       

      Best Regards,
      Liang
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • admin_xlsior's avatar
        admin_xlsior
        Post Prodigy
        5 years ago

        Hi V-lianl-msft ,

         

        So you're saying Distinct makes this Crossfilter not working ? Yeah, actually I would love to share the PBIX, problem is I'm using Direct Query, and when I switch to import, comes some error also the rows of all the tables (especially FACT) is a lot.

        Let me try to share it, if possible.

         

        Thanks