Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I've tried to look at the forum for an answer, but without luck.
So i'm hoping someone is able to help me.
I have the following data example:
YEAR TYPE COMPANY DATE
2018 SALES Company1 2018-01-01
2018 SALES Company1 2018-01-01
2018 OFFER Company1 2018-01-01
2018 SALES Company1 2018-01-02
2018 SALES Company2 2018-01-02
2018 OFFER Company2 2018-01-03
2017 SALES Company1 2017-01-01
2017 SALES Company2 2017-01-01
I want to show the number of daily unique sales.
Meaning for Company1 I want to show 2, instead of the 3 I'm getting as a result right now.
Thanks in advance,
Jim
Solved! Go to Solution.
Sorry, I replied to your post in haste. What I did was, I created a concatenated column:
Column = TableName[TYPE] & TableName[COMPANY] & TableName[DATE]
And then I created a measure using DISTINCTCOUNT:
Measure = Calculate(DISTINCTCOUNT(TableName[Column]),TableName[TYPE]="SALES")
and it worked. Please try.
You should be able to do this without any DAX formulas at all.
Thanks for your solution.
However I really need a measure for this.
Sorry, I replied to your post in haste. What I did was, I created a concatenated column:
Column = TableName[TYPE] & TableName[COMPANY] & TableName[DATE]
And then I created a measure using DISTINCTCOUNT:
Measure = Calculate(DISTINCTCOUNT(TableName[Column]),TableName[TYPE]="SALES")
and it worked. Please try.
Thank you all for your replies.
I got the solution ow, thanks!
For uniqueness you could try DISTINCTCOUNT instead of COUNT
Measure = Calculate(COUNT(Table[TYPE]),Table[TYPE]=SALES)
SALES should be enclosed in double quotes
Measure = Calculate(COUNT(Table[TYPE]),Table[TYPE]="SALES")
Hello,
Are you looking for the count considering unique combination of Company Name, Date and Type? Another way to say the same: Is this what you are looking for?
YEAR | TYPE | COMPANY | DATE | COUNT |
2018 | SALES | Company1 | 01-01-2018 | 2 |
2018 | OFFER | Company1 | 01-01-2018 | 1 |
2018 | SALES | Company1 | 02-01-2018 | 1 |
2018 | SALES | Company2 | 02-01-2018 | 1 |
2018 | OFFER | Company2 | 03-01-2018 | 1 |
2017 | SALES | Company1 | 01-01-2017 | 1 |
2017 | SALES | Company2 | 01-01-2017 | 1 |
Thank you for your reply.
Sort of, I'm looking to remove duplicates for the combination of Company Name, Date and Type.
In your table the sum of all SALES in 2018 is 4, but I only want to show 3 (as there is a duplicate on date 2018-01-01).
Thanks again.
Can you please clarify what you mean by daily unique sales, and how you want it broken down, by company date etc.
Sure, let me try:
I'm trying to count how many sales has been made to each company per year. But only 1 sales per day should be counted towards this number. This is due to the technical where each line item is added instead of only showing the one total line.
So from my data I have the following sales to Company1 in 2018:
YEAR TYPE COMPANY DATE
2018 SALES Company1 2018-01-01
2018 SALES Company1 2018-01-01
2018 SALES Company1 2018-01-02
But since the two first are from the same date, it should only count as one.
So my current result is showing 3, but I want to only show 2.
I hope it makes sense now, otherwise please reply.
Thanks again.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |