The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
Please I want a way to make a distinct count by criteria for each day during the month.
Example:
Thank you in advance.
Solved! Go to Solution.
sorry, it was the wrong table.
here is the correct one.
DistinctCountofSameID on Same day =
CALCULATE(DISTINCTCOUNT( 'db'[Identfier]),
db[STATUS]="Accepted",
KEEPFILTERS('db'[STATUS]="Accepted"))
I am not sure if I understood your question.
We have created a custom column that will create the Identifier. So, it should work. May be you can test with some more data. Btw, first try then see, if the above one solution that I have provided is not working, you can wait for some other's solution.
Cheers
sorry, it was the wrong table.
here is the correct one.
DistinctCountofSameID on Same day =
CALCULATE(DISTINCTCOUNT( 'db'[Identfier]),
db[STATUS]="Accepted",
KEEPFILTERS('db'[STATUS]="Accepted"))
The solution works (y)?
Last question in case there are several criteria it will be like this?
DistinctCountofSameID on Same day =
CALCULATE(DISTINCTCOUNT( 'db'[Identfier]),
db[STATUS]="Accepted", db[criteria2]="criteria2",
KEEPFILTERS('db'[STATUS]="Accepted",db[criteria2]="criteria2",))
Try this:
Create a Calculated Column
Identfier =
CONCATENATE('db'[ID CLT] ,'db'[DATE])
then create this measure
DistinctCountofSameID on Same day =
CALCULATE(DISTINCTCOUNT( 'db'[Identfier]),
sumtable[STATUS]="Accepted",
KEEPFILTERS('db'[STATUS]="Accepted"))
Thanks a lot.
What is "sumtable", for me it's grey?
Failed to resolve the name "sumtable". This is not a valid table, function or variable name.
Hi,
Create a Calendar Table. Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table. To your Table visual, drag Date column from the Calendar Table. Write this measure
Measure = calculate(distinctcount(Data[ID CLT]),Data[Statut]="Accepted")
Alternatively, you may create a slicer of Statut and select Accepted in that slicer. Then write this measure
Measure = distinctcount(Data[ID CLT])
Hope this helps.
Hello,
I didn't quite understand the solution, but in the end I want a result like this
Thank you very much.
Hi,
What problem do you face when implementing my solution?
So as per your above example, for 3rd, you want to see 3 as Accepted and for 4th 3 as Accepted?
First of all, thank you for your answer.
No I want the same result.
for the 3rd, I want to see 2 as accepted and for the 4th 2 as accepted.
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 |
---|---|
102 | |
96 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |