Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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:
I think I should have a ralationship which envolves Table3 but how?!?
bye
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
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
Thanks @Vvelarde, could you please let me see how you've defined ParamSelected and RangeSelected measures?
bye!
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]))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |