Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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:   ...
Need Help
bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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