Forum Discussion

simormate's avatar
simormate
Frequent Visitor
5 years ago

show a calculated table in a visual

There is a measure which shows the number of service contracts that fulfill a set of criteria: the date the contract started, the price plan it is in, the sales person responsible, etc. The measures is used in visuals using the date dimension, and they use calculate with the userelationship modifier. I need this for example because the date column is used either for the date of the sale or for the start date of the contract of that sale.

 

This is the measure I am using:

New contracts with range price plan =
CALCULATE (

COUNTROWS ( Contracts ),
PricePlans[Range] = TRUE(),
Partners[Parent ID] = BLANK(),
USERELATIONSHIP ( 'Calendar'[Date], Contracts[Contract Start Day] ),
USERELATIONSHIP ( Contracts[First Price Plan ID], PricePlans[Price Plan ID] )

)

 

I would like to create a drill-through from this measure. After clicking on the drill-through of the first measure, I would like to see the individual records (contracts) in a column, and their attributes that are in the criteria of the measure, such as first price plan, contract start date, in the following columns

 

I could create a table containing Contract ID, price plan, etc. and then point the drill-through to this table. However, when I click on the drill through, it applies the filter of the value clicked. As some of the filters were applied only through the USERELATIONSHIP modifier, these filters will not apply on the table that will be visible after the drill-through.

 

I could define what I would like to see with a calculated table, but how to show the result in a power bi visual?

5 Replies

    • simormate's avatar
      simormate
      Frequent Visitor

      Thanks, I tried to rewrite my question to make it clearer.

  • Hi simormate ,

     

    In Power BI drill-through works as follows:

    1. Create 1st page which has got a table visual with all the summary elements.
    2. Create a 2nd page which act as a Drill-through page from 1st Page when drilled in to details by drilling for example against a metric from the table visual on 1st page and then seeing it's details on 2nd page.

    You can refer following blog on how drill-through works in Power BI to get an idea on how to visualise this functionality:

    https://powerbi.tips/2018/09/drillthrough/

     

    Thanks,

    Pragati

     

    • simormate's avatar
      simormate
      Frequent Visitor

      Yes, I knew this - the problem is that I would like to see individual records of a dataset after drilling through, and filtered not only with the filter context of the value that was right clicked, but also with the filters defined within the calculate function of the original measure.

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User

        Hi simormate ,

         

        One way of doing this could be placing the FILTERS that are in CALCULATE function for this measure, under KEEP FILTERS section of drill-through page along with the field on which drill-through is created:

         

        Thanks,

        Pragati