Forum Discussion

GaryK's avatar
GaryK
Frequent Visitor
9 years ago
Solved

Visualization Interactions - Is this possible?

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

DateStart_SEQEnd_SEQ
1-Oct-201612
8-Oct-201636
12-Oct-201679

 

DETAIL

SEQValue
1bicycle
212345
3potato
4xyz
5Number1
6circle
7Elephant
8android
9red
10New 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

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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



3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    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.

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    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's avatar
      GaryK
      Frequent Visitor
      9 years ago

      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