Distinctcount based on unique key

[Jdiep]

Hello,

I have been wrecking my brain over this and tried various solutions on this platfrom but can't seem to figure it out. 

I have a column with a unique key, but one key appears multiple times because the transaction is registired on that key. With that transaction is also logged what type of fuel was bought with that transaction. 

I need a function (column) that willen tell me if one unique key has had transactions for two types of fuel or more. See an example below. Hope to hear from and thank in advance. 

PS the filter on the first column is to get the 0 and empty values out.

[v-alq-msft]

Hi, @Jdiep 

Could you please show me your sample data? Do mask your sensitive data before uploading. Thanks.

Best Regards

Allan

[Jdiep]

Hi,

Thank you for your reply. I cant seem to upload files to the community. Would you like a screenshot of the columns that are in the file? Or should I copy more rows into a reply so you can work with it? Let me know. 

[v-alq-msft · Accepted solution]

Hi, @Jdiep 

Based on your description, I created data to reproduce your scenario.

Table:

Then you may create a column or a measure as follows. I hope it may help you.

calculated column:
Two>fueltype column = 
IF(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Unique Number] = EARLIER('Table'[Unique Number])
        )
    )>=2,
    "equal or more than two",
    "only one"
)

measure:
Two>fueltype measure = 
IF(
    COUNTROWS(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Unique Number] = MAX('Table'[Unique Number])
        )
    )>=2,
    "equal or more than two",
    "only one"
)

You may display the result with a table visual.

Best Regards

Allan

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

[Jdiep]

Hi Allen,

Thank you so much. This had been bothering me for days. Kudos to you!

Regards,

Jur

[Anonymous]

Do you mind copy and pasting this as data? Or sharing the file?