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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to count distinct number of days with filters and slicers

I am trying to count number of days on which the delay_group equal to "A"  on specific dates selected by user using date slicer and bank number. "A" groups are indicated as "black font" in matrix.  if I select dates from 25-06-2020 to 5-07-2020 the numbers of days having A should return '5' , instead of it is returning "7" . I have attached DAX meausre used to calculate the count of days , the table used to calculate the distinct count and the visual I am using :

The visual :

poblem.pngproblem 2.pngproblem3.png

4 REPLIES 4
dedelman_clng
Community Champion
Community Champion

Hi @Anonymous  -

 

I've always had trouble with COUNT functions in a CALCULATE / FILTER context, Usually I've found it's best to not use explicit FILTER statements inside CALCULATE if at all possible, instead letting CALCULATE do the work (as a side note, if I am doing a COUNTROWS, I move the filters as much as possible inside to the first argument of COUNTROWS - I know we can't do that here, but just saying).

 

So try your measure without the explicit filters

 

...CALCULATE(DISTINCTCOUNT('New Table'[Bank_Date],
   DATESBETWEEN ( ...                  //this one is OK,
   'DimTime'[IsWorkingDay] = TRUE,    //might even be able to get away with just referencing the column since it's boolean
   'New Table'[delayGroup] = "A"
)

 

If this doesn't help, can you share your pbix or some at least some sample data?

 

David

Anonymous
Not applicable

Thanks for the response . I am not sure if I am writing the measure wrong all the way . But let's just narrow down the problem. I have this below table as a sample :

Bank_R   Bank_Date      endtime           delayGroup   IsDeadlinePassed 
47/6/20207/7/2020 11:58On time0
197/6/20207/7/2020 14:47On time0
197/6/20207/7/2020 9:12Delayed1
47/7/20207/8/2020 12:50On time0
197/7/20207/8/2020 15:16On time0
197/7/20207/8/2020 9:56Delayed1


I want to calculate number of days and number of banks which where 'On time' OR whose deadline was not passed which in this case is '0' .

 

In the table above what I should get as a result is :

 

No of banks on time = 1 (which is Bank_R = 4 in this case)
No of days on time = 2 (which is 7/6/2020 and 7/7/2020)


Besides this Bank_R and Bank_Date can be taken as user selection from slicers so I don't know if I can do something with Summarize. I am trying to play around with something like this below but it is of no use :

number of bank days.png

 

Please help 😞 

 

Can you share your pbix? Need to check on things like relationships to your date table, different datatypes, etc.

Anonymous
Not applicable

I just added some filter conditions with calculate and worked . I wasn't able to share the pbix file due to data goverance issue. thanks for you prompt reply. much appreciated 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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