Forum Discussion

Sir_night's avatar
Sir_night
Frequent Visitor
9 years ago
Solved

Excluding results from table

So i have a table which has a multi select date filter But i want exculed rows where a movment key matchs and refrence matchs

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    You could create a column in your dataset called CombinedKay, which is just the Reference and MovementKey combined:

    Create a new measure called DuplicateCount. This will get the count over all time for the CombinedKeyCombo. You may have to change ALL to ALLSELECTED if you are using a slicer on the SnapshotDate:

     

    DuplicateCount = CALCULATE(COUNT(Sheet1[CombindedKey]), all(Sheet1[SnapshotDate]))

     

    Then, in your grid on the report, add a visual filter where DuplicateCount = 1:

     

    All records:

    Filtered Records:

     

     

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Sir_night,

     

    If I understand you correctly, you should be able to firstly use the formula below to create a new measure, then use the measure as a visual level filter([Measure] is 0) to exclude rows where a movement key matches and reference matches. :smileyhappy:

    Measure =
IF (
    FIRSTNONBLANK ( Table1[Reference], 1 ) = 10014474
        && FIRSTNONBLANK ( Table1[MovementKey], 1 ) = "X-VOID-OCCP",
    1,
    0
)

    Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You could create a column in your dataset called CombinedKay, which is just the Reference and MovementKey combined:

    Create a new measure called DuplicateCount. This will get the count over all time for the CombinedKeyCombo. You may have to change ALL to ALLSELECTED if you are using a slicer on the SnapshotDate:

     

    DuplicateCount = CALCULATE(COUNT(Sheet1[CombindedKey]), all(Sheet1[SnapshotDate]))

     

    Then, in your grid on the report, add a visual filter where DuplicateCount = 1:

     

    All records:

    Filtered Records:

     

     

    • Sir_night's avatar
      Sir_night
      Frequent Visitor
      9 years ago

      hi olesojg

       

      My count does not work, i've tried ALLSELECTED and ALL See below

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Sir_night,

         

        Sorry for my misunderstanding previously. :smileylol:

         

        Based on my test, if you use ALL(Table1[SnapshotDate]) instead of All(Table1[SnapshotDate].[Date]) in your formula then the measure should work as expected. :smileyhappy:

        DuplicateCount = CALCULATE(COUNT(Table1[CombinedKey]), ALL(Table1[SnapshotDate]))

         

        Regards