Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CrisSalgado
Frequent Visitor

Use value resultant from multiple filters to filter another table

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!

4 REPLIES 4
amitchandak
Super User
Super User

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.

 

 

Greg_Deckler
Super User
Super User

@CrisSalgado So, assuming those are all source tables, what would be the expected output?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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  

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.