Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi, I am struggeling with thefollowing table format:
Control Number | Title |
PPG-E0001 | A |
PPG-E0001 | A |
PPG-E0002 | C |
PPG-E0002 | C |
PPG-E0002 | C |
PPG-A0001 | F |
PPG-A0001 | F |
PPG-A0001 | F |
PPG-C0001 | I |
I want to use a Dax function in order to do the following things:
1. Count only unique values of the Row Control Number (Not duplicates)
2. After that I want to be able count only the values which include for example "C" or "A" or "E"
At the end I should get a measure for example for "PPG-E" = 2
I ve tried with DISTINCTCOUNT function but didnt get it.
To be honest I am a beginner with Power BI and would appreciate any help for this problem.
Thx for ur help
Solved! Go to Solution.
Hi @Yarisch77
this will take whole two months in account.
It will take last date from previous month to today.
I think your requirement is same day from last month to today.
I have provided answer for the same.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hi @Yarisch77
if i understand you correct
try measures:
1.
unique values = CALCULATE(DISTINCTCOUNT(Table[Control Number]))
2.
unique values by Title = CALCULATE(DISTINCTCOUNT(Table[Control Number]), ALLEXCEPT(Table, Table[Title]))
Hi,
Thx for ur quick reply. This goes into a good direction. I took ur idea and played around a little. AFter a while I got this:
Hi @Yarisch77
Measure = CALCULATE(DISTINCTCOUNT('Table'[Control Number]),filter(table,SEARCH("A";'Table'[Control Number];;-1)>0 && table[date]>=date(2019,1,1) && table[date]<=date(2019,1,1) && table[Status]="Completed"))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hey,
Great formula, but it gave me a syntax error. Here's is the adapted formula with the original values from my company, may u see the error:
Could you share what syntax error you got?
It may be some syntax issue like missing closing parenthesis or comma.
Thanks,
Pravin
It says....
Wrong Syntax für "SEARCH".(DAX(CALCULATE(DISTINCTCOUNT('CPro Server'[Control Number]),filter('CPro Server'SEARCH("A",'CPro Server'[Control Number],,-1)>0 && 'CPro Server'[Action Request Due Date]>=date(2019,1,1) && 'CPro Server'[Action Request Due Date]<=date(2019,1,1) && 'CPro Server'[Status]="Completed"))).)
Hi @Yarisch77
you have missed comma (,) after table name in filter section.
Try to write dax by yourself step by step so that you will get know more about issue.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hey,
Thx a lot , works perfect now. Just one last question:
If i am going from this...
Hi @Yarisch77
Last 30 days or 1 month = CALCULATE(DISTINCTCOUNT('CPro Server'[Control Number]);filter('CPro Server';SEARCH("A";'CPro Server'[Control Number];;-1)>0 && 'CPro Server'[Action Request Due Date]>=dateadd(today(),-1,month) && 'CPro Server'[Action Request Due Date]<=Today() && 'CPro Server'[Status]="Completed"))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hi @Yarisch77
instead of dateadd(today(),-1,month)
you can use date(year(today()),month(today)-1,day(today()))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
This actually could also work, giving the same result for the las 60 days or not?
Hi @Yarisch77
this will take whole two months in account.
It will take last date from previous month to today.
I think your requirement is same day from last month to today.
I have provided answer for the same.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Thx for ur reply,
The formula gives me the following error:
The first argument to 'DATEADD' must specify a column
...
User | Count |
---|---|
24 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
20 |