cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Filtering Table

Hi, I've got these three table:

 

TABLE 1

Id  Param

1     Area

2     Volume

 

 

TABLE 2

ParamId     Range

   1             0-100

   2             0-500

 

 

TABLE 3

Id  Area  Volume   Description

1     52       102           Item1

2    300      800           Item2 

 

 

and I've got two filters one for column Param (TABLE 1) and one for column Range (TABLE 2), I would like to display all TABLE 3's items with Area and Volume columns satisfying filters selection. How can I do?

 

Thanks a lot in advance for any clue!

 

Luca
8 REPLIES 8
Vvelarde Super Contributor
Super Contributor

Re: Filtering Table

@laciodrom_80

 

hi Luca:

 

First in table 2 create a new column with the Max Value of the Range.

 

TABLE 2

ParamId     Range  MAX

   1             0-100      100

   2             0-500      500

 

Now create a measure:

 

FilterIndicator =
IF (
    [ParamSelected] = "Area",
    IF ( MIN ( Table3[Area] ) <= MIN ( Table2[Max] ), 1; BLANK () ),
    IF (
        [ParamSelected] = "Volume",
        IF ( MIN ( Table3[Volume] ) <= MIN ( Table2[Max] ), 1; BLANK () )
    )
)

And use in the visual filter. 

 

Needs some adjustment to refine the filters selection but give you a light in the way.

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




v-huizhn-msft Super Contributor
Super Contributor

Re: Filtering Table

Hi @laciodrom_80,

In your table, the data type of Range field is text, right? If it is, I suggest you split the column in Power Query Edit. Please see the following screenshot.

1.PNG

You will get the max value of range.

2.PNG

In addtion, I try to reproduce and create the measure as the @Vvelarde posted, I will post the update if I find new solution.

Best Regards,
Angelia


Re: Filtering Table

Thanks @Vvelarde for your suggestion, I can't make FilterIndicator measure valid (I've renamed your [ParamSelected] with Table1[Param]), probably because I have not all necessary relationships between tables: actually I've got only this relationship:

 

  • Table1[Param]  with Table2[ParamId] 

I think I should have a ralationship which envolves Table3 but how?!?  Smiley Frustrated

 

bye

 

 

 

 

Luca

Re: Filtering Table

@v-huizhn-msft

 

Hi Angela, no problem in finding Max value, as you can see in my answer at @Vvelarde I've got some doubts about the measure, in particular how to releate Table3 with others ones

 

bye

Luca
Vvelarde Super Contributor
Super Contributor

Re: Filtering Table

@laciodrom_80

 

hi Luca

 

Relationships.png

 

Don't have relationships.

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Filtering Table

Thanks @Vvelarde, could you please let me see how you've defined ParamSelected and RangeSelected measures?

 

bye!

Luca
Vvelarde Super Contributor
Super Contributor

Re: Filtering Table

@laciodrom_80

 

Of course, i forgot to include in my answer

 

ParamSelected = IF(HASONEVALUE(Table1[Param]),VALUES(Table1[Param]))
RangeSelected = IF(HASONEVALUE(Table2[Range]),VALUES(Table2[Range]))



Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Filtering Table

Thanks @Vvelarde,  but how can I use FilterIndicator measure to display in a table all Table3's items which satisfy ParamSelected and RangeSelected conditions? A measure returns a scalar...is there a trick?

 

Bye

Luca

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 170 members 2,298 guests
Please welcome our newest community members: