Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi, all!
I need some help to filter a table using value resultant from other filtes.
I have four tables:
- tbDT contains a list of datetime values:
- tbsource contains fields: idsource and sourcename,
- tbvalue contains fields: datetime (FK from tbDT), idsource (FK from tbsource) and value.
- tbcause contains fields: datetime (FK from tbDT), idsource (FK from tbsource) and level. Column level is an integer that we use to sort the idsources for each datetime.
What complicates:
- same idsource can be level 2 and level 4 for the same datetime, for example.
- same idsource can be level 2 for datetime "x" and level 3 for datetime "y"
- an idsource may exist on tbcause in a datetime but do not exist in another datetima
- tbcause and tbvalue has an NxM relationship
In my dashboard, a datetime is selected and I present 6 cards. First card present the value (from tbvalue) for idsource level = 1 and datetime selected. Second card present the value (from tbvalue) for idsource level = 2 and datetime selected. And so on until last card.
I also have 4 line charts (trends) where I want to show all values (from tbvalue) for the idsource presented on each card. Line chart 1 shows data from idsource filtered on card 1 and no filter for datetime. Line chart 2 has the same behavior with card 2.
Trying to put some values.... Datetime is represented as an integer (an iddatetime)
tbDT tbsource
iddatetime idsource sourcename
1 1 A
2 2 B
3 3 C
4 4 D
5
tbValue tbcause
iddatetime idsource valor iddatetime idsource level
1 1 6.1 1 1 1
1 2 7.0 1 1 2
1 3 7.9 1 4 3
... ... ... 2 2 1
3 1 4.0 2 4 2
3 2 2.9 3 4 1
.... .... .... 3 3 2
4 2 6.6 3 1 3
4 3 9.8 4 2 1
4 4 5.6 4 3 2
..... .... .......
6 1 8.0
6 2 6.5
6 3 6.9
For example, if iddatetime=3 is selected, I want linechart 1 shows all values contained on tbValue for idsource = 4, linechart 2 should show all trend for idsource 3, etc...
If datetime = 1 is selected then linechart 1 and 2 will show the same data that is information from tbValue for idsource = 1
We already tried to filter using the level, but what happens in this case is, for example, is iddatetime selected =1, linechart1 will plot information from idsources 1, 2 and 4 because all of them were level 1 in some point....
Thanks for your help!
@CrisSalgado , Not very clear to me. But you plan to change Axis use bookmark and if you want change measure you can use Slicer
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
Hi,@amitchandak. I'm not trying to modify the axis... Talking about the link of slicer, the user do not select an item of an slicer. It is selected a datetime and adding it to other filters applied to the card, the information on teh card should filter the line chart...
I added a new comment trying to explain better.
@CrisSalgado So, assuming those are all source tables, what would be the expected output?
Hi, Greg_Deckler and all,
What I expect to see is each line chart plotted with values from tbValue for the idsource filtered on the card from tbcause.
The dashboard has a slicer where the user can select a datetime. Table datetime has an iddatetime which is already present on tbcause and tbvalue.
The dashboard also has 4 cards. The iddatetime selected filters the tbcause, what results in 4 registers, with levels 1 to 4. One card filters this result to show the sourcename (tbsource) of the idsource which is level =1. Another card shows the sourcename of idsource that is level=2.... Keep in mind that is for the iddatetime selected only. If iddatetime changes, others 4 idsources will be filtered in cause. Up to here, we were able to do.
What I can't do:
The dashboard also has 4 line charts. First line chart should present the values and datimetime from tbvalue filtered by the idsource that is shown in card. No filter in datetime will be applied to tbvalue. I want to show the trend specifically of the idsource filtered on the first card, no matter if this tag wasn't the first level in other datetimes or even if it was in tbcause in other datetimes.
Using the data in the post, if iddatetime=4 is selected by the user, first card will show "B" - 6.6, that is the sourcename and value of idsource=2 and iddatetime=4, second card will show "C" - 9.8 that is idsource=3 and iddatetime=4 ..... (sorry, the example do not fill all cards but we can use only 2 as example). OK. This is working.
Now comes the part that is not working:
I want linechart 1 shows all values contained on tbValue for idsource = 4, which means the pair of (iddatetime, value) = (1, 7.0), (3, 2.9), (4, 6.6), (6, 6.5). Linechart 2 must show all values for idsource = 3, whiche means (1, 7.9 ), (4, 9.8), (6, 6.9)
I
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |