Create Dynamic Tables based on slicer from the same data set and compare them

[Anonymous]

Hi All,

I'm trying to create 2 dynamic tables based on a 'From Date' , 'Status' and a 'To Date', 'Status' selection

If someone wants to know, how many people moved from Active to Lapsed from April to June

A3 and A4  had a status of Lapsed in April while they are Active in April

Is this possible in PowerBI?

How can I create 2 slicers from 1 column from the same data set?

How can I create dynamic tables based on slicer values and join them to compare

@amitchandak  @Fowmy  @v-eqin-msft  @v-rzhou-msft  @Vera_33  @Jihwan_Kim  @mahoneypat  @ImkeF  @DenisSlav   : Could any of you please help

[israelfc92]

Muchas gracias, tambien me ayudo!

[Anonymous]

@PaulDBrown  Thank you so much, this is exactly what I was looking for!   Could you please explain what "Filter Contact ID" = 1 does?

[PaulDBrown]

The [Filter Contact ID] measure works as follows:

VAR fromtable creates a virtual table which lists the Contact ID values from the main table which fulfill the conditions set by the "From" slicers

VAR totable creates a virtual table which lists the Contact ID values from the main table which fulfill the conditions set by the "to" slicers

INTERSECT is a function which returns a virtual table including only the Contact IDs present in both virtual tables created in the VARs

Finally COUNTROWS will return a value of 1 for each row present in the virtual table created by INTERSECT.

You can then use this measure in the filters for the visual. By setting the filter value to 1, the visual will only show the Contact IDs from the main table which have been listed by the INTERSECT function (in other words, those which fulfill both the "From" and "To" selections in the slicer).

To improve user experience, you can create another filter for the "To Month" slicer to show only the months which are after the month selection made in the "From Month" slicer. To do this, I have included an index column in the slicer tables (which also allow for proper sorting).

And use this measure in the filters for the "To Month" slicer, setting the value to 1:

filter "to month slicer" = IF(MAX('To Month'[Index]) > SELECTEDVALUE('From Month'[Index]), 1)

and you get this:

As you can see, only the months after the month selected in the "From month" are listed in the "To month" slicer.

Hope that helps, and let us know if you need further help.

[Anonymous]

@PaulDBrown  Awesome, this really helps..thanks again for the detailed explanation

[PaulDBrown · Accepted solution]

Create 2 sets of disconnected tables with unique values for month and status (I've prefixed them "From" and "To"). The model shuld look like this:

Next create a measure to use as a filter for the visual as follows:

Filter Contact ID =
VAR fromtable =
    CALCULATETABLE (
        VALUES ( 'Table'[Contact ID] ),
        FILTER (
            'Table',
            'Table'[Month] = SELECTEDVALUE ( 'From Month'[Month] )
                && 'Table'[Status] = SELECTEDVALUE ( 'From Status'[Status] )
        )
    )
VAR totable =
    CALCULATETABLE (
        VALUES ( 'Table'[Contact ID] ),
        FILTER (
            'Table',
            'Table'[Month] = SELECTEDVALUE ( 'To Month'[Month] )
                && 'Table'[Status] = SELECTEDVALUE ( 'To Status'[Status] )
        )
    )
RETURN
    COUNTROWS ( INTERSECT ( fromtable, totable ) )

Create measures to identify the filtering for the visual following this structure:

From Month =
SELECTEDVALUE ( 'From Month'[Month] )

Finally reate the visual with the Contact ID field form the main table and add the measures for "From" and "To". In the filter pane, add the [Filter contact ID] measure to the filters for the visual and set the value to 1:

I've attached the sample PBIX file for you