Showing only names with 1 ID

[PandaOnASlide]

Hi there,

I have a list of Agent Names and their Commission type. An Agent can have just commission type 1 OR BOTH 1 and 2 (if they have 2, then they have 1)

I'm trying to isolate only the agents with a commission type of 1, but having trouble since EVERY agent has a 1. Something like "if an agent has both 1 and 2, don't show." But not sure how to create that.

Any ideas? I've attached a picture below with some names and IDs. You can see agents appear twice, but then Amy B only has commission type 1.  

[Ashish_Mathur · Accepted solution]

Hi,

If every agent who has 2 also has 1, then why does Amy have only a 2 (in the image)?  Anyways, try this measure

Measure = distinctcount(Data[CommissionTypeID])

In the filter pane, apply a condition of 1 on this measure.

Hope this helps.

[PandaOnASlide]

There are some exceptions given how an agent enters the business - sorry for any confusion there!

I'll try this out, thank you!

[AbhinavJoshi]

Hello @PandaOnASlide. One way to achieve it using Group By in power query m. This is my sample dataset 

As per your requirement only John Doe and Smith Doe should be isolated. 

I use a group by 

Then I filter

Finally Expand all 

You can remove/add columns as required. 

I hope it helps. 

Thanks,

Abhinav

[PandaOnASlide]

Thank you for this!

[rsbin]

@PandaOnASlide ,

Please try this.  Create a Calculated Column that counts the number of Commission ID's for each Name:

CommissionCount = CALCULATE( DISTINCTCOUNT( Commission[CommissionTypeId] ),
                        ALLEXCEPT( Commission, Commission[FullName] ))

Then filter this new column for 1's only.  Is this what you are looking to achieve?

Regards,

[PandaOnASlide]

Hi there,

I tried this but it didn't seem to work. You're showing a DAX code but it looks like the columns are in PQ and the language is different. Can you elaborate a bit more please?

[rsbin]

@PandaOnASlide ,

Correct, this is DAX code to create a Calculated Column.

The image is that of a Table View to demonstrate the results of the new Column.

Hope this answers your questions.

Regards,

[PandaOnASlide]

Thank you!