Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

Filter Power BI data based on Selected filter range value

Hi Folks!! I have table data as below    Project Type Year Measure P-1 A 2017 100 P-1 A 2017 50 P-1 A 2017 10 P-1 A 2018 20 P-1 A 2018 55 P-1 A 2019 75 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  amsrivastavaa ,

 

Here are the steps you can follow：

1. Create calculated table.

Year_Slicer =
DISTINCT('Table'[Year])

2. Enter data – Create table.

3. Create measure.

value =
var _selectyear=SELECTCOLUMNS('Year_Slicer',"Year",[Year])
var _sunmall=
SUMX(FILTER(ALL('Table'),
'Table'[Year] in _selectyear&&'Table'[Type]=MAX('Table'[Type])),[Measure])
return
DIVIDE(MAX('Table'[Measure]),_sunmall)
Flag =
var _selectrange=SELECTEDVALUE('Range_Slicer'[Range])
var _selectyear=SELECTCOLUMNS('Year_Slicer',"Year",[Year])
return
SWITCH(
TRUE(),
MAX('Range_Slicer'[Range])="<25%"&&[value]<0.25&&MAX('Table'[Year]) in _selectyear,1,
MAX('Range_Slicer'[Range])="25-50%"&&[value]>=0.25&&[value]<0.5&&MAX('Table'[Year]) in _selectyear,1,
MAX('Range_Slicer'[Range])="50-75%"&&[value]>=0.5&&[value]<0.75&&MAX('Table'[Year]) in _selectyear,1,
MAX('Range_Slicer'[Range])="75-100%"&&[value]>=0.75&&[value]<1&&MAX('Table'[Year]) in _selectyear,1,0)

4. Place [Flag]in Filters, set is=1, apply filter.

5. Result:

If you need pbix, please click here.

 

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

  • amsrivastavaa's avatar
    amsrivastavaa
    Helper III
    3 years ago

    Hi Anonymous ,

    Thanks for the response.

     

    Here are few things which is required as a part of solution.

     

    1. When I am not selecting any values in either YEAR and Range filter, nothing comes up as shown below, ideally it will suppose to display complete set of data , please suggest

     

    2. When I am selecting multiple Year say 2017 and 2018,VALUE need to be calculated based on VALUE/(sum of VALUE of that year), i.e. if there is value in 2017 year, it will be calculated as VALUE/[sum(VALUE) of 2017], and for YEAR 2018, it must be VALUE/SUM(VALUE) of 2018.

     

    However, it works well when I am selecting only ONE YEAR say 2017 in Year Filter, but it doesn't works well as soon as i will select another year say 2018 alongwith 2017, it shows incorrect values, as shown below.

     

     

    i.e. In first snapshot-1, where only year selected is 2017, it shows value as 6.25% which is correct however in snapshot-2, I have selected 2017 and 2018. value shown as 4.25% which is incorrect, it suppose to 6.25$ only, please suggest!!

     

    Please assist and suggest!!

     

    Thanks

    Amit