Forum Discussion

yaronklein's avatar
yaronklein
Icon for Helper II rankHelper II
4 years ago
Solved

Filtering with advance mode when there are multi values doesn't work

Hi,

I have a one to many relationship between 2 tables: Issues and Labels, which means of course that one issue can have several different labels.


I would like my users to be able to filter out using the default Filters pane (on the right side of the report) all the issues that have a specific label value.
This works well when an issue has only that label, however when there are multiple labels per Issue, these specific Issues still appear.
I tried to use the advanced filter and to use ‘does not contain’, but this doesn’t work as I expected.

 

An example:
In this example I have 2 issues (5049 and 5429). Issue 5049 has 2 labels and Issue 5429 has only one label.
I would like to filter out issues with label ‘PostAlpha’.
The table below shows the Issues when all labels are selected:

 

 

Once I used the advance filter ‘does not contain’ I was hoping that I will get no Issues in the table, but as you can see I get the instance with NAC label.

 

I understand why it happens, do you have a way to solve it so the filter will remove all records?

Thanks in advance.

 

Thanks,
Yaron

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, yaronklein ;

    You could create two measure then filter into visual.

    1.create a table about [Issue_Key] as slicer.

    Table = VALUES('Issue'[Issue_Key])

    2.create two measures:

    Measure = 
IF(MAX('Labels'[Issue_key]) in ALLSELECTED('Table'[Issue_Key]),CALCULATE(COUNT('Labels'[Label_name]),FILTER(ALL('Labels'),[Issue_key]=MAX('Labels'[Issue_key]))))
    Flag = 
COUNTX (
    FILTER (
        ALL ( 'Labels' ),
        [Issue_key] = MAX ('Issue'[Issue_Key]) &&[Issue_key]=MAX('Labels'[Issue_key])
        &&
        ([Label_name] IN SUMMARIZE ( FILTER ( ALL ( 'Labels' ), [Measure] = 1 ), Labels[Label_name])
        || NOT([Issue_key])  in ALLSELECTED('Table'[Issue_Key])))
    ,
    [Issue_key])

    3.apply it into filter.

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, yaronklein ;

    You could create two measure then filter into visual.

    1.create a table about [Issue_Key] as slicer.

    Table = VALUES('Issue'[Issue_Key])

    2.create two measures:

    Measure = 
IF(MAX('Labels'[Issue_key]) in ALLSELECTED('Table'[Issue_Key]),CALCULATE(COUNT('Labels'[Label_name]),FILTER(ALL('Labels'),[Issue_key]=MAX('Labels'[Issue_key]))))
    Flag = 
COUNTX (
    FILTER (
        ALL ( 'Labels' ),
        [Issue_key] = MAX ('Issue'[Issue_Key]) &&[Issue_key]=MAX('Labels'[Issue_key])
        &&
        ([Label_name] IN SUMMARIZE ( FILTER ( ALL ( 'Labels' ), [Measure] = 1 ), Labels[Label_name])
        || NOT([Issue_key])  in ALLSELECTED('Table'[Issue_Key])))
    ,
    [Issue_key])

    3.apply it into filter.

    The final output is shown below:

     


    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.