Forum Discussion
Solved
DAX help
Original data table has 3 columns - SiteID, time, IFHO_INTRA. need to calculate the number of days IFHO_INTRA >=98 under a new column "Pass days" the below table shows data table filtered from 6 Ja...
prashantg364
i) How come 3810 will be 2? it should be 2. Check your data once again.
ii) It is not possible to dynamically apply slicer selections to a calculated column. Slicers impact visuals by defining the filter context, whereas calculated columns are evaluated during data refresh. both are different concepts.
if you want to have slicer impact on your table then suggesting you create measure instead of calculated column.
For measure you can refer below codePass days= VAR _1 = CALCULATE( COUNTROWS( FILTER( 'Table',[Total Intra] > 98 )) , ALLSELECTED( 'Table'[time] ) ) VAR _Result = IF( ISBLANK( _1 ),0, _1 ) RETURN _Resultbelow screenshot
For calculated column, you will have to manually select the last five days and then create the calculated column which johnt75 has provided the solution.
let me know if you need pbix file.
Regards
sanalytics
You can create a calculated column like
Pass Days =
VAR Last5Days =
WINDOW (
1,
ABS,
5,
ABS,
DISTINCT ( 'Table'[time] ),
ORDERBY ( 'Table'[time], DESC )
)
VAR Result =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[SiteID] ),
Last5Days,
'Table'[IFHO_INTRA] >= 98
)
RETURN
Result