Visualization Interactions - Is this possible?

[GaryK]

I'm very new to Power BI and am struggling with getting teh result that I need...

Basically, I have 2 tables/datasets - SUMMARY and DETAIL.

There is no 1-1 mapping/relationship between the 2 tables.

SUMMARY

Date	Start_SEQ	End_SEQ
1-Oct-2016	1	2
8-Oct-2016	3	6
12-Oct-2016	7	9

DETAIL

SEQ	Value
1	bicycle
2	12345
3	potato
4	xyz
5	Number1
6	circle
7	Elephant
8	android
9	red
10	New Year

I have a column chart that displays the SUMMARY data and a table that displays the DETAIL data.

What I want to do is when the user clicks on a column in the SUMMARY chart, the DETAIL table should only show the data where the SEQ falls in the Start_SEQ to End_SEQ range.

For example, if I click on "1-Oct-2016" on the SUMMARY chart, the DETAIL table should only show "bicycle" and "12345".

Thinking from a DB query persepctive, this would look something like 

SELECT Value

FROM DETAIL

WHERE SEQ between Start_SEQ and End_SEQ

Is this possible with Power BI, and if so, please point me in the right direction.

Many Thanks

Gary

[v-haibl-msft · Accepted solution]

@GaryK

You can do some transforms for your SUMMARY table in Query Editor. Then you can get your desired result directly.

1. Duplicate the DETAIL table, rename it to SEQ, delete the Value column.
   
2. In SUMMARY table, add custom column as below.
   
3. Expand the custom column.
4. Add another custom column with following formula. Change the data type of these two columns to Whole Number.
   

   if [Custom.SEQ] >= [Start_SEQ] and [Custom.SEQ] <= [End_SEQ] then [Custom.SEQ] else 0

   
5. Filter the Custom column, only show rows do not equal 0.
   
6. Remove the Custom column.
7. Close and apply Query Editor, create relationship between DETAIL and SUMMARY with SEQ and Custom.SEQ key.
   
8. We only need to drag columns into charts. Table chart will be filtered since there is proper relationship between them. I’ve also upload my PBIX file here for reference.
   
   
   

Best Regards,

Herbert

[GaryK]

Thanks Herbert_Liu for taking the time to provide detailed expanation and screenshots.

My data is more complex than the simple scenario that I provided, and I must admit I struggled a bit with you implementing your solution into my real world scenario, but I got there in the end.

Cheers

Gary

[Vvelarde]

hi @GaryK

You can do it this creating a measure to filter the details table.

Measure =
VAR Start_Seq =
    IF ( HASONEVALUE ( Summary[Date] ), VALUES ( Summary[Start_SEQ] ) )
VAR End_Seq =
    IF ( HASONEVALUE ( Summary[Date] ), VALUES ( Summary[End_SEQ] ) )
RETURN
    IF (
        HASONEVALUE ( Detail[SEQ] ),
        IF (
            VALUES ( Detail[SEQ] ) >= Start_Seq
                && VALUES ( Detail[SEQ] ) <= End_Seq,
            1,
            0
        )
    )

Then add this measure to visual level filters and select is 1.

Don't forget to delete the measure of the table visual.