topic Count rows with 5 filters of various types in DAX Commands and Tips

Count rows with 5 filters of various types

KarenLL — Thu, 16 Jun 2022 22:18:37 GMT

Hi All

I am having trouble trying to work out what measure I need to calculate the following:

I need to count rows in a table when column 1 = 0 & column 2 = column 3 (date type) & column 4 = column 5 (time type) & column 6 is blank (date type) & column 7 is blank (date type).

Any assistance would be very appreciated.

Thank you.

Karen

Re: Count rows with 5 filters of various types

ribisht17 — Fri, 17 Jun 2022 04:11:58 GMT

KarenLL

Please try this with your own filters

Measure 4 = COUNTROWS( CALCULATETABLE( SUMMARIZE(orders_,orders_[Category],orders_[Sub-Category],"Count",DISTINCTCOUNT(orders_[Sub-Category])),orders_[Category]="Technology" || orders_[Category]="Office Supplies" ))

Regards,

Ritesh

Re: Count rows with 5 filters of various types

KarenLL — Sat, 18 Jun 2022 06:08:05 GMT

Thanks ribisht17. Where would my filters come in to it?

My Worktime column has to equal 0 AND loadeddate column = lastupdated column AND loadedtime column = lastupdatedtime column AND completeddate column has to be blank (contain no date) and the exceptionreason column has to be blank(contain no text) THEN I need to count the rows. I am not sure how your measure filters the above items? If you could explain it, that would be great.

Thank you.

Re: Count rows with 5 filters of various types

ribisht17 — Sat, 18 Jun 2022 10:56:35 GMT

KarenLL

Please post some data here to get the answers

Ritesh

Re: Count rows with 5 filters of various types

KarenLL — Sun, 19 Jun 2022 03:15:54 GMT

ribisht17 attached is some sample data. Hope this helps.

The column names are:

1 - ID

2 - Worktime

3 - Loaded Date

4 - Loaded Time

5 - Last Updated Date

6 - Last Updated Time

7 - Completed Date

8 - Exception Reason

Count should equal 3 (rows 2, 3 and 5) using the following filters:

Worktime = 0 AND

Loaded Date = Last Updated Date AND

Loaded Time = Last Updated Time AND

Completed Date = blank AND

Exception Reason = blank

1 2 3 4 5 6 7 8

1	60	16/02/2022	23:26:20	18/02/2022	3:28:14	18/02/2022	Business Exception
2	0	20/02/2022	1:15:22	20/02/2022	1:15:22
3	0	7/09/2021	5:30:15	7/09/2021	5:30:15
4	2	24/12/2021	15:02:48	27/12/2021	19:25:14	27/12/2021	Business Exception
5	0	25/03/2022	10:40:32	25/03/2022	10:40:32
6	0	8/05/2022	18:45:31	15/06/2022	23:48:32	15/06/2022	System Exception
7	0	8/05/2022	15:26:17	15/06/2022	16:58:16		System Exception

Re: Count rows with 5 filters of various types

Anonymous — Tue, 21 Jun 2022 06:56:07 GMT

Hi KarenLL ,

Please refer to my pbix file to see if it helps you.

Create a measure and a column.

Measure = calculate(COUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Worktime]=0&&'Table'[Loaded Time]=SELECTEDVALUE('Table'[Last Updated Time])&&'Table'[Completed Date]=BLANK()&&'Table'[Exception Reason]=BLANK()))

Column = CALCULATE(COUNT('Table'[ID]),FILTER('Table',[Measure]<>BLANK()))

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.