How to make a dynamic bar chart affect a table's filter context without Calculated Columns?

DAX
1

I have a model for Payables Ageing Analysis with:

  1. A Bar Chart
  2. A Pie Chart
  3. A Table
  4. A dynamic date slicer

The date slicer sets “Date Selected” which allows for a “Days Left” per transaction to be dynamically calculated:

Days Left = 
//Days left where Selected Date >= Posting Date AND (Selected Date < Clearing Date OR = BLANK)
IF( AND( [Posting Date] <= [DateSelected], 
    OR([Clearing Date] > [DateSelected], [Clearing Date] = BLANK())),
   DATEDIFF( [DateSelected],[Due Date], DAY ),
   BLANK() )

The chart uses this code to then place the transactions in buckets for the bars:

_A0_ PayablesPerGroup_Chart = 
CALCULATE(
    [_A0_ OutstandingAmount],
    FILTER( 
        _FACT_BSEG_AP,
        COUNTROWS(
            FILTER( 
                'AgedGroups_Chart',
                IF(
                    ( 'AgedGroups_Chart'[Min] < 0
                    || 'AgedGroups_Chart'[Min] == 0 )
                    && ( 'AgedGroups_Chart'[Max] > 0
                    || 'AgedGroups_Chart'[Max] == 0 ),
                ( ( [Days Left] < 0
                    && [Days Left] >= 'AgedGroups_Chart'[Min] )
                    || [Days Left] == 0 )
                    || ( ( [Days Left] > 0
                    && [Days Left] <= 'AgedGroups_Chart'[Max] )
                    || [Days Left] == 0 ),
                [Days Left] >= 'AgedGroups_Chart'[Min]
                    && [Days Left] <= 'AgedGroups_Chart'[Max]
            )
        )
    ) > 0
)
)

(Same sort of code for the Pie Chart.)

The problem:

I would like the Bar Chart to filter the table.

So if the end user clicks the 30+ bar, they should only see the transactions with days left greater than 30.

Why not Calculated Columns?

I know this is theoretically possible by creating calculated columns for Days Left and the Groupings/buckets and then connecting a Grouping Table to the Fact Table.

However, I am dealing with 1.5 million rows in the live dataset. Performance is already an issue, so having two calculated columns recalculate each time somebody touches the slicer would take far too long to process I fear.

Is there some other way of doing this that would not require Calculated Columns?

Demo File:

Dynamic_Chart_Interaction_Table.pbix (1.0 MB)

2

Hi @OniScion - Check attached file. Created a new measure as below and used it as filter on the Table visual. Now, Table visual will show data based on Groups selected on the bar chart. if nothing is selected, it will show all the records.

Days Left Test = var SelectedGroup = If(ISFILTERED(AgedGroups_Chart[Groups]),min(AgedGroups_Chart[Min]),BLANK())

return if(ISBLANK(SelectedGroup),1,if([Days Left] > SelectedGroup,1,0))

Dynamic_Chart_Interaction_Table.pbix (1.0 MB)

Thanks
Ankit J

1 Like
3

Thank you, that worked!

Now I finally understand the point of IsFiltered()!

Well, the logic at least. Your solution takes all min and above so I have included another version which filters the days left based on the min and max in the chart filter tables.

Not too sure whether the declared variables in my edit still require an if-not-then-blank pattern based on vFiltered, but it seems like it would improve performance (to be tested).

For the sake of completing this question, I also fixed the values in the chart filter tables as they were haphazardly mixing </> and <=/>= logic and I added a Days Left Test 3 for the Pie Chart (the same thing as the bar chart).

Days Left Test 2 = 

var vIsFiltered = ISFILTERED(AgedGroups_Chart[Groups])

var MinSelectedGroup = 
                If(
                    vIsFiltered,
                            min(AgedGroups_Chart[Min]),
                            BLANK()
                    )
var MaxSelectedGroup = 
                If(
                    vIsFiltered,
                            max(AgedGroups_Chart[Max]),
                            BLANK()
                    )

return

IF(
    vIsFiltered,
       IF(
                ( [Days Left] >= min(AgedGroups_Chart[Min])
                    && [Days Left] <= max(AgedGroups_Chart[Max])
                    ),
                1,
                0
        ),
        1
)

Dynamic_Chart_Interaction_Table_Solution.pbix (1.0 MB)