topic Re: Dax on filtering duplicates and counting with text in DAX Commands and Tips

Dax on filtering duplicates and counting with text

Yarisch77 — Tue, 03 Mar 2020 06:25:39 GMT

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

Re: Dax on filtering duplicates and counting with text

az38 — Tue, 03 Mar 2020 07:00:55 GMT

Hi Yarisch77

if i understand you correct

try measures:

unique values = CALCULATE(DISTINCTCOUNT(Table[Control Number]))

unique values by Title = CALCULATE(DISTINCTCOUNT(Table[Control Number]), ALLEXCEPT(Table, Table[Title]))

Re: Dax on filtering duplicates and counting with text

Yarisch77 — Tue, 03 Mar 2020 08:10:07 GMT

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:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Control Number]);SEARCH("A";'Table'[Control Number];;-1)>0)

This worked well without counting duplicates and only counting with letter "E". Now I added 2 more rows with additional Infos => "Date" and "Status". The idea is to follow the above Measure but put two conditions:

1. if "Date" is between for example "01.01.2019 and 20.01.2019"

2. and if "Status" is for example "Completed"

Thx in advance for ur help

Re: Dax on filtering duplicates and counting with text

Anonymous — Tue, 03 Mar 2020 08:15:07 GMT

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.

Re: Dax on filtering duplicates and counting with text

Yarisch77 — Tue, 03 Mar 2020 08:30:27 GMT

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:

Measure 2 = 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"))

Thx in advance for ur help

Re: Dax on filtering duplicates and counting with text

Anonymous — Tue, 03 Mar 2020 08:42:47 GMT

Could you share what syntax error you got?

It may be some syntax issue like missing closing parenthesis or comma.

Thanks,

Pravin

Re: Dax on filtering duplicates and counting with text

Yarisch77 — Tue, 03 Mar 2020 08:45:51 GMT

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"))).)

Re: Dax on filtering duplicates and counting with text

Anonymous — Tue, 03 Mar 2020 08:58:19 GMT

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.

Re: Dax on filtering duplicates and counting with text

Yarisch77 — Tue, 03 Mar 2020 09:56:09 GMT

Hey,

Thx a lot , works perfect now. Just one last question:

If i am going from this...

Measure 2 = 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;12;1) && 'CPro Server'[Status]="Completed"))

...and instead to define a date range I want to specify for example "last 30 days from today" or "comming 90 days from today", how do I do that???

Thx a lot in advance

Re: Dax on filtering duplicates and counting with text

Anonymous — Tue, 03 Mar 2020 10:49:20 GMT

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.

Re: Dax on filtering duplicates and counting with text

Yarisch77 — Thu, 05 Mar 2020 09:33:15 GMT

Thx for ur reply,

The formula gives me the following error:

The first argument to 'DATEADD' must specify a column

...

Re: Dax on filtering duplicates and counting with text

Anonymous — Thu, 05 Mar 2020 09:40:11 GMT

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.

Re: Dax on filtering duplicates and counting with text

Yarisch77 — Thu, 05 Mar 2020 09:59:26 GMT

This actually could also work, giving the same result for the las 60 days or not?

Measure 4 = CALCULATE(DISTINCTCOUNT('CPro Server'[Control Number]);filter('CPro Server';SEARCH("A";'CPro Server'[Control Number];;-1)>0 && 'CPro Server'[Action Request Due Date]>= EOMONTH(TODAY();-2) && 'CPro Server'[Action Request Due Date]<=Today() && 'CPro Server'[Status]="Completed"))

....

Re: Dax on filtering duplicates and counting with text

Anonymous — Thu, 05 Mar 2020 10:54:07 GMT

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.