Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinctcount based on unique key

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.

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    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.

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

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

     

    Best Regards

    Allan

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, Anonymous 

         

        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.