Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter logic which filters other filter dynamically

Hi All,

can you help in acheiving the below challenge which i am facing -
below is the sample data what we have in our table.
if user selects biz_id=1 then team_id should filter 2 and show only 2nd row
if user selects biz_id=2 then team_id should filter 4 and show only last 2 rows.

 

logic should be somewhat similar to this

if (  biz_id=1 and team_id=2) or  (  biz_id=2 and team_id=4) then show the respective rows

 

seq_idbiz_idteam_idproduct
111a1
212a2
311a2
421a1
522a2
623a3
724a1
824a2

 

Thanks,

Raj

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    To be clear, measure can only be used as a visual level filter.

    Try create a calculated column.

    col = var max_team_id =  CALCULATE(MAX('Table'[team_id]),ALLEXCEPT('Table','Table'[biz_id]))
return IF('Table'[team_id]=max_team_id,1,0)

     

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Assuming biz_id Id will filter because of slicer value

     

    measure =
    var _team = if(selectedValue([biz_id]) =1 ,2 ,4)

    return
    calculate(Countrows(Table), filter(Table,Table[team_id] =_team))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      yes biz_id can be a filter but i am not able to get the desired output with this solution. can you pls share the pbix file.

      output should should be the table based on below condition.

      if ( biz_id=1 and team_id=2) or ( biz_id=2 and team_id=4) then show the respective rows

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Create a measure like this and apply it to visual level filter.

    Measure = var max_team_id =  CALCULATE(MAX('Table'[team_id]),ALLEXCEPT('Table','Table'[biz_id]))
return IF(MAX('Table'[team_id])=max_team_id,1,0)

    Sample .pbix

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Liang.

       

      This 'Measure' working fine only for visual level filter pane. but we have muliple pages and multiple visuals in each page.

      when i try to add the 'Measure' in the page level filter pane, its not getting added.

       

      can you please help. how can i add this 'Measure' which should work across all pages.

       

      Thanks,

      Rajesh

       

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        To be clear, measure can only be used as a visual level filter.

        Try create a calculated column.

        col = var max_team_id =  CALCULATE(MAX('Table'[team_id]),ALLEXCEPT('Table','Table'[biz_id]))
return IF('Table'[team_id]=max_team_id,1,0)

         

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