Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Drill through with internal SELECTEDVALUE

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 d...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!