cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sir_night Helper I
Helper I

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

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
olesojg Resolver II
Resolver II

Re: Excluding results from table

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

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:

DuplciateCounts.png

Filtered Records:

DuplciateCounts2.png

 

 

View solution in original post

5 REPLIES 5
Microsoft v-ljerr-msft
Microsoft

Re: Excluding results from table

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. Smiley Happy

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

r2.PNG

Regards

olesojg Resolver II
Resolver II

Re: Excluding results from table

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

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:

DuplciateCounts.png

Filtered Records:

DuplciateCounts2.png

 

 

View solution in original post

Sir_night Helper I
Helper I

Re: Excluding results from table

hi olesojg

 

My count does not work, i've tried ALLSELECTED and ALL See below1.PNG2.PNG

Sir_night Helper I
Helper I

Re: Excluding results from table

still not working

Highlighted
Microsoft v-ljerr-msft
Microsoft

Re: Excluding results from table

Hi @Sir_night,

 

Sorry for my misunderstanding previously. Smiley LOL

 

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. Smiley Happy

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

r2.PNG

 

Regards

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors