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 tab...
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



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