Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
You can do some transforms for your SUMMARY table in Query Editor. Then you can get your desired result directly.
if [Custom.SEQ] >= [Start_SEQ] and [Custom.SEQ] <= [End_SEQ] then [Custom.SEQ] else 0
Best Regards,
Herbert
You can do some transforms for your SUMMARY table in Query Editor. Then you can get your desired result directly.
if [Custom.SEQ] >= [Start_SEQ] and [Custom.SEQ] <= [End_SEQ] then [Custom.SEQ] else 0
Best Regards,
Herbert
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
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.
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |