Filtering Table

[laciodrom_80]

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!

[Vvelarde]

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

[laciodrom_80]

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?!?  

bye

[v-huizhn-msft]

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.



You will get the max value of range.



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

[laciodrom_80]

@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

[Vvelarde]

@laciodrom_80

hi Luca

Don't have relationships.

[laciodrom_80]

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

bye!

[Vvelarde]

@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]))

[laciodrom_80]

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