Forum Discussion

victor_erathos's avatar
victor_erathos
Frequent Visitor
4 years ago

Filter aggregation visual by ID field

My question revolves around two tables, one with the information of each distinct product and another with the amount of users associated with each product in different cities, such as the following ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  victor_erathos ,

Here are the steps you can follow：

1. Create measure.

User_city =
var _selectcity=SELECTEDVALUE('Users'[city])
var _selectrange=SELECTEDVALUE('pRange'[range])
var _usercity=CALCULATE(SUM('Users'[users]),FILTER(ALL('Users'),'Users'[city]=_selectcity&&'Users'[barcode]=MAX('Products'[barcode])))
return
IF(
_usercity =BLANK(),0,1)
Min =
var _selectrange=SELECTEDVALUE('pRange'[range])
var _min=CALCULATE(MIN('pRange'[lower_limit]),FILTER(ALL(pRange),'pRange'[range]=_selectrange))
return
_min
Max =
var _selectrange=SELECTEDVALUE('pRange'[range])
var _max=CALCULATE(Min('pRange'[upper_limit]),FILTER(ALL(pRange),'pRange'[range]=_selectrange))
return
_max
Flag111 =
IF(
    [User_city] >=[Min]&&[User_city]<=[Max],1,0)

2. Put [Flag111] in Filter and set is=1

3. Result:

Please click here for the pbix file

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

victor_erathos's avatar
victor_erathos
Frequent Visitor
4 years ago

I am not sure I understand your approach to the problem. It still does not seem that I can create a bar chart as I mentioned in the post — agregated by category with only the relevant products (please also bear in mind that I created a simplified example, in my actual data I have more than 100k unique products).