Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
saurabh1484
Frequent Visitor

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

I have three tables

Table 1 ( Release)

VersionStartEnd
5001-01-202131-01-2021
5101-02-202128-02-2021
5201-03-202131-03-2021

 

Table 2 Customer 

TicketNumberOpenVersionCreatedDateResolved
14512-05-202013-06-2020
24818-10-202012-01-2021
34912-12-202015-01-2021
44915-12-202012-02-2021
54920-12-202003-01-2021
64925-12-2020 
75005-01-202115-01-2021
85007-01-202115-02-2021
95015-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 

3 REPLIES 3
Anonymous
Not applicable

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

Anonymous
Not applicable

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors