Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Hi, @Jdiep
Could you please show me your sample data? Do mask your sensitive data before uploading. Thanks.
Best Regards
Allan
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.
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.
Hi Allen,
Thank you so much. This had been bothering me for days. Kudos to you!
Regards,
Jur
Do you mind copy and pasting this as data? Or sharing the file?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |