Drillthough and filtering issue

[cg1980]

Hi Guys,

This can be silly.. but I didnt find a way.. attached a sample file with my problem,* I have Millons of records , this is  an example to illustrate what i want to achive )

PBIX file

My table containins order id , product id and unit price, I need to generate a list showing the product ID and the number of products included in the orders which value was zero (free goods)  

so far , so good.. My problem is that i need to provide a functionality that based on user selection on product ID on "Example output table"  , (in this example Product ID 10 )

i need to return a table with Order ID, Product ID, and UNIT PRICE of all the orders where one of the line items contains the Product ID = 10, ( in other words, all the line items of all the orders that contain that product which value was zero)

In the example provided the desired output would be all the the items for Order ID 1 and Order ID 2 ( Order ID 3 does not contain Product ID 10 and shouldnt be in the output)

Any advise on how to approach this issue?

[v-rzhou-msft · Accepted solution]

Hi @cg1980 

Drillthrough function will only show you the value which contains product ID= 10 only without showing all product ID in the same Order Id which contains product ID =10.

You can achieve your goal by measure and Sync slicers in Vew Tab.

Firstly build a new table named Table2 (is not related to Table1):

Table 2 = SUMMARIZE('Table','Table'[Order ID],'Table'[Product ID])

Then update your measure and build a new measure.

# of Orders elements with prod. unit price 0 = 
VAR _Count = CALCULATE(COUNT('Table'[Order ID]),'Table'[Unit Price]=0)
VAR _OrderID =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Unit Price]=0 ),
        'Table'[Order ID]
    )
Return
IF(MAX('Table 2'[Order ID]) in _OrderID,_Count,BLANK())

Measure = 
VAR _Sel =
    SELECTEDVALUE ( 'Table 2'[Product ID] )
VAR _OrderID =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Product ID] = _Sel ),
        'Table'[Order ID]
    )
RETURN
    IF (
        ISFILTERED ( 'Table 2'[Product ID] ),
        IF ( MAX ( 'Table'[Order ID] ) IN _OrderID, 1, 0 ),
        1
    )

Drag measure into filter field and set to show items when the value =1 as below.

Select product ID =10 in Visual 2 and see the result in the same page.

If you want to filter on other pages you need to build a slicer by product ID in Table2 and drag measure(# of Orders elements with prod. unit price 0 ) in filter field and set to show items is not blank.

Then use sync slicer function, select the pages you want this slicer impact.(You need to add measures in visuals' filter field in other pages as well)

You can download the pbix file from this link: Drillthough and filtering issue

Best Regards,

Rico Zhou

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

[cg1980]

@v-rzhou-msft  Thanks a millon! your solution is elegant and perfect! 

[amitchandak]

@cg1980 , Not very clear to me. Assume you have order id an product id in the table and when drill through you do not want to pass order id. On the drilled page you have drill through the filter, There do not add or remove order id.

If you need value =0 or [measure]= 0 . For value =0 you can use visual level filter. Same is true for measure.

But if measure is blalnk then add +0 to measure and then filter =0 at visual level

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough

[cg1980]

Hi @amitchandak @Ashish_Mathur   I believe i know how to do standard drill throughts, do I ? 🙂 

What I need  is to filter the table using product id indirectly , if i traslate this to SQL I would need the target table to be populated with : 

"Select * from table where order_id in (select order id from Table where product_id =" User Selection") "

In the example i provided

Product ID	order Elements with Zero Value
10	2

then the user click on the product ID = 10 

below  is the outupt I exepect either in an other page or in the same,  all the orders Ids  where product id = 10 is present including all the other products that were part of the order. Make sense? 

Order ID	Product ID
1	10
1	11
1	12
1	13
1	14
2	10
2	11
2	12
2	13
2	14

[Ashish_Mathur]

Hi,

Someone else will help you with this one.

[Ashish_Mathur]

Hi,

You have already written the formula to get your desired result.  What is the concern/problem?