Forum Discussion
Calculate working days from multiple rows on the same date
JoshuaMartinezP Try this. PBIX is attached below signature.
No. of Work Days = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table', [TS is work day] = TRUE),"Date",[Date])))
JoshuaMartinezP Wait a minute, is that third calculation a calculated column or a measure? Because if it is a calculated column generally does not work with measures because the calculated column only gets calculated at the time of data load.
'No. of Work Days' is a measure.
'TS Day Name' and 'TS is work day' are calculated columns, built from the 'Date' column.
My aim is to display the number in a card that updates when I make a selection in my slicers. At the moment, because of my 'Date Table', the number updates with the dates correctly:
But selecting a slicer from my main dataset doesn't effect the 'Date Table' results and therefore the number of working days stays unchanged.
That's why I need to have the measure base the working days on the dates in the dataset. Unfortunately there are multiple rows per working day and so I need to find a way to filter them in the calculation.
JoshuaMartinezP Try this. PBIX is attached below signature.
No. of Work Days = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table', [TS is work day] = TRUE),"Date",[Date])))