Drill through with internal SELECTEDVALUE

[Anonymous]

Hi,

I have a question in regards to drill through between two pages, each with a different dataset.

For context, I have two dataset: Billing and Booking. Given the complexity of the data, I did not make any kind of relationship between the two datasets as of now.

Instead, I mostly used measure column and/or calculated column when I wanted to use data from both datasets in one visual. One example of the measure column is this:

Monthly Order =

var period = SELECTEDVALUE(Billing[FY_PERIOD])

var booking_value = CALCULATE(SUM(Booking[USD_AMOUNT]), FILTER(Booking, Booking[FY_PERIOD] = period))

return

booking_value

This measure column calculates the total order from the booking dataset I have in each period and display it in a table that uses billing dataset. Something like this (I'm using Excel to demo how the Table looks like in Power BI:

So I have FY_PERIOD from Billing dataset, Monthly Sales which is merely SUM(Billing[USD_AMOUNT]) and %Shipping which is the percentage of Monthly Sales/Monthly Order. I use this table to gauge the partial shipment trend in each financial period.

Here comes the difficult part for me: I want to use the drill through function to further analyze the data. I am planning to make two reports from this table. Something like Monthly Sales Details and Monthly Order Details. The Monthly Sales Details report is easy, but I have been trying to set the Monthly Order Details for days now and I'm still heading nowhere. 

The idea feels so simple yet so hard. I just need to somehow "extract" the FY_PERIOD value from the billing dataset, and use it to filter another dataset in another page for column of the same name. 

Is there a way to do this? Or I'm just being naive and there are other more proper ways?

[johnt75]

Create a proper date table, marked as a date table, and link that to both tables. Use values from the date table in your visuals, and as the drillthrough field, and everything should work correctly.

[Anonymous]

Hi @johnnt75

Thank you! That actually... hm... works to a certain extent. And Coicidentally, I have been working with a similar solutions all this while. 

With this approach, I face two prominent problems:

1. The value for Monthy Sales became incorrect the moment I made the relationship. I'm not sure why it behave that way. Unfortunately I can't really show you the screenshot of my Power BI due to it containing sensitive data. I can only tell you that the calculation for Monthly Sales is a simple SUM(Billing[USD_AMOUNT]). 

2. Other than that, I also made some calculations to segregate those sales that is delivered within the period and those outside of the period. For some contexts, aside from FY_PERIOD, I also have other time categories like FY_QUARTER and FY_SEMESTER. As the names suggest, its to categorize orders and sales within the quarter and within half a year. I have different visuals for different time categories, and I want the visual to not have any sales duplicate. For example, if a sales is delivered within the period, then it will not appear in the FY_QUARTER. 

Because drill through only accepts visual filter, I make a calculated field for each time category to compare the the value. Something like:

CHECK_PERIOD =

var book_period = CALCULATE (

    FIRSTNONBLANK ( Booking[FY_PERIOD], 1 ),

    FILTER ( ALLSELECTED(Booking[ORDER_NUMBER]), T_BK[ORDER_NUMBER] = Billing[SALES_NUMBER] )

)

return

IF(Billing[FY_PERIOD] = book_period, 1, 0)

So for visual with FY_PERIOD, CHECK_PERIOD = 1. Similarly, visual with FY_QUARTER, CHECK_PERIOD = 0; CHECK_QUARTER = 1. So and so for...

My datasets are abit complicated. To cut the story short, one booking can have many rows depending on the number of items (I know, its a bad dataset design as it violates normalization. I was not in the company when it was created). And one booking can have many FY_PERIOD as customer can just keep adding more items or cancel items in the same order (also a bad business principle but I can't change how it works). So I used FIRSTNOBLANK to get the earliest FY_PERIOD of the booking, then I use that to do my checkings in my calculated fields.

However, the relationship somehow messes up the formulas and ended up just copy-pasting values from the Billing dataset. Something like:

Before relationship (also the expected output):

After relationship (incorrect output):

I don't know what happened. And my gut is telling me that this is the culprit why my Monthly Sales is also such a mess up post-relationship.

[johnt75 · Accepted solution]

I would recommend that you identify which dates you want to report on, e.g. order date and delivery date, and then decide how those dates are determined. You mention that items can be added to orders on different days, so is the order date the date that the first item was ordered? If items can be delivered at different times, is the delivery date the first or last date on which items were delivered?

Once you have decided the above, you need to make sure that there is a column in your fact table for each date, and that each order has the same value for those columns. As an example you could create a column like

Order date for reporting =
CALCULATE (
    MIN ( 'Bookings'[Order date] ),
    ALLEXCEPT ( 'Bookings', 'Bookings'[Order ID] )
)

You would then create relationships from the date table to these new columns. Only 1 relationship can be active at once, so you would need to decide whether the primary relationship was based on order date or delivery date.

It would probably be a good idea to create a calculation group with 2 calculation items, 1 based on the primary relationship, which would simply call SELECTEDMEASURE(), and the other based on the inactive relationship, e.g.

CALCULATE( SELECTEDMEASURE(), USERELATIONSHIP( 'Date'[Date], 'Bookings'[Delivery date for reporting]) )

You could then use this to switch visuals between reporting by delivery or order date.

[Anonymous]

Hi @johnt75 

It works! You are a life saver!

I ended up marrying your formula with mine. FY_PERIOD is a text so I ended up with:

CALCULATE(FIRSTNONBLANK(Booking[FY_PERIOD], 1), ALLEXCEPT(Booking, Booking[ORDER_NUMBER]))

I didn't do the calculation group though. I think its just so happen that my time categories has a hierarchy (FY_PERIOD < FY_QUARTER < FY_HALFYEAR < FY_YEAR) so I just used FY_YEAR to create the relationship and all 4 of my visuals work at the end 🙂

Again, thanks so much!

[Anonymous]

Hi @John 

There are few reasons why the FY_PERIOD is in place, main one being that the dataset has a lot of missing order date and sales date. The dataset is a compilation of data from many sources - legacy to acquisition.

But your suggestion gives me an idea. Perhaps the same concept can be applied to the FY_PERIOD, where i can just standardize the period of one order and use that for reporting. 

Let me try this out. If it works, I will post it here 🙂 Thanks alot! I feel like I can see a silver lining.