topic Re: Count all Records of a table using some condition depending on slicer value in DAX Commands and Tips

Count all Records of a table using some condition depending on slicer value

saurabh1484 — Sat, 22 May 2021 10:58:45 GMT

I have three tables

Table 1 ( Release)

Version	Start	End
50	01-01-2021	31-01-2021
51	01-02-2021	28-02-2021
52	01-03-2021	31-03-2021

Table 2 Customer

TicketNumber	OpenVersion	CreatedDate	Resolved
1	45	12-05-2020	13-06-2020
2	48	18-10-2020	12-01-2021
3	49	12-12-2020	15-01-2021
4	49	15-12-2020	12-02-2021
5	49	20-12-2020	03-01-2021
6	49	25-12-2020
7	50	05-01-2021	15-01-2021
8	50	07-01-2021	15-02-2021
9	50	15-01-2021

Table 3 - CalendarTable

CalendarTable Date column is related with CustomerTable Created date

Release Table versioncolumn is related with CustomerTable OpenVersion column

I have Release Version slicer which is selected to 50

Requirement

I want to count the records of customer table where createddate < release 50 startdate and resolveddate > release 50 package date

I can add filter for createddate < r50 startdate like count(customer[ticket], calendar[date] < r50 startdate) but since there is no relation with resolved date I can add anything for resolved date.

ANy suggestion how to do that? Do I need to create some new relation?

I want to find the number of records for

Re: Count all Records of a table using some condition depending on slicer value

Anonymous — Sat, 22 May 2021 11:11:42 GMT

Just go with COUNTROWS(FILTER(yourtable,yourcondition))

Re: Count all Records of a table using some condition depending on slicer value

Anonymous — Sat, 22 May 2021 11:18:55 GMT

I think I don't understand fully your issue. If you want to use a different date to relate with calendar for a certain measure, you should have an inactive relationship between dates and use CALCULATE(..., USERELATIONSHIP('Date' [Date], ticket[resolved Date])) or something similar

Re: Count all Records of a table using some condition depending on slicer value

saurabh1484 — Sat, 22 May 2021 12:57:53 GMT

This will not work for me.

My understanding and requirement is

1. User set Slicer to Release 50.
2. Release 50 start date is 1 Jan 2021 and end date is 31 Jan 2021 {From Release table}.
3. Since there is a relation bewteen release table and customer table on the basis of version, customer table is filtered to show records where openversion is 50.

However my Requirement is to find records from unfiltered Customer table where createddate < 1 Jan 2021 and (resolved date > 31 Jan 2021 or Blank) which should return 2 records.