cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StaceyLGriffeth
Frequent Visitor

Drillthrough based on disconnected table

I have a model where there is a fact table of transactions, a date table (related to fact table), and a disconnected date table (used for filtering).  The model structure is like the below:

 

StaceyLGriffeth_0-1646253989805.png

 

I have a report page filtered by the Disconnected Date Table.  I have a second page for drill-through.  I would like the drill-through to filter both for the selected account as well as the month of the trx date equal to the month of the selected value from the disconnected date table.  I am stuck on the latter... I have posted a mocked up dummy pbix here.  I appreciate any suggestions you may have.

 

Thanks!

 

4 REPLIES 4
bcdobbs
Super User
Super User

You need to pick the filter that gets passed up and "manually" apply it to the visual.

I wrote the following measure:

VisualFilter = 
VAR SelectedMonthEnd = SELECTEDVALUE ('DisconnectedDate'[MonthEndDate] )

VAR Result = 
    CALCULATE (
        INT ( NOT ( ISEMPTY ( Transactions ) ) ),
        'Date'[MonthEndDate] = SelectedMonthEnd
    )

RETURN Result


It reads the selected month end from the filter context and then checks if the transaction table is empty when MonthEndDate gets filtered by it. The NOT flips the logic found so it returns TRUE when there are records and then INT turns it to a 1/0.

Which can then be placed in the visual filter on your drill through page set equal to 1:

 

bcdobbs_0-1646256765841.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@bcdobbs Thank you so much for your suggestion!  I tried the first one:

 

VisualFilter =
VAR SelectedMonthEnd = SELECTEDVALUE ('DisconnectedDate'[MonthEndDate] )

VAR Result =
CALCULATE (
INT ( NOT ( ISEMPTY ( Transactions ) ) ),
'Date'[MonthEndDate] = SelectedMonthEnd
)

RETURN Result
 
I put that measure on a card and saw that it does accurately change 1/0 based on selection.  However, when I added it as a filter on the drillthrough visual, the drillthrough did not filter correctly.  When a date is selected from the slicer, the drillthrough is blank.  Do you have any suggestions?

Can you share your updated pbix. Will have a look.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Same idea but following would be more flexible if multiple months were selected:

VisualFilter = 
VAR SelectedMonthEnds = 
    TREATAS (
        VALUES ('DisconnectedDate'[MonthEndDate] ),
        'Date'[MonthEndDate]
    )

VAR Result = 
    CALCULATE (
        INT ( NOT ( ISEMPTY ( Transactions ) ) ),
        SelectedMonthEnds
    )

RETURN Result


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors