Forum Discussion

osindhuw1's avatar
osindhuw1
New Member
3 years ago

Dynamic Multi-Column Filter based on one parameter

Hi All,

 

I am looking for assistance to make a dynamic filter in my visual. My data is look like this below picture, basically consists of item code, month, year, and data checker for each departure and arrival, and lastly the quantity.

 

I am creating 1 parameter called "Parameter month", consisted of Departure (represent month departed column) and Arrival (represent month arrived column). I use it as slicer, so whenever i select Departure, my graph will visualize total quantity based on month departed column, and so do with Arrival. The measure to summarized the total quantity based on the selected slicer is done and the visual works perfectly.

 

The problem now is, i want to filter only for item departed/arrived (based on slicer) in 2023 and only OK data. So whenever i choose "Departure" in  slicer, my visual will only show all data with Year departed = 2023 and Departure OK = 1, which is the sum of quantity is 20.

 

Same goes when i choose Arrival slicer, my visual will only show data with Year arrived = 2023 and Arrival OK = 1, with the sum of quantity of 16 (regardless whatever the departure status is)

 

I tried to create a new parameter year, and put it in the filter, but it seems the filter only show the column name instead of the value of the column, makes filtering not possible. Any advise to create this dynamic filter, without adding any new slicer in the visual page?

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  osindhuw1 ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
var _table=
{"departed","arrived"}
var _table2=
DISTINCT('Table'[Year departed])
return
CROSSJOIN(
_table,_table2)

    2. Create measure.

    Flag =
var _selectvalue=SELECTEDVALUE('Table 2'[Value])
var _selectyear=SELECTEDVALUE('Table 2'[Year departed])
return
SWITCH(
    TRUE(),
    _selectvalue = "arrived"&&MAX('Table'[Year arrived])=_selectyear&&MAX('Table'[Arrival OK])=1,1,
    _selectvalue = "departed"&&MAX('Table'[Year departed])=_selectyear&&MAX('Table'[Departure OK])=1,1,0)

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

    4. Result:

    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

    • osindhuw1's avatar
      osindhuw1
      New Member
      3 years ago

      Hi Anonymous ,

       

      First of all thank you very much for your prompt reply. I have check your model and it seems good.
      But the problem now is, the flag is somehow cannot be connected with other visual chart, which using parameter (for separator) instead of the created table value. Both table and visual need to be filtered with the same flag, which same rule, 2023 year and data OK (departed or arrived one, depend on selection).

      I attach the files so you can relate (somehow i cannot attach the pbix), please advise?