Slicer instead of filter with calculated column

[maximd]

Hi,

The table below shows the amount of breakdowns for a specific date range with the report details.
DepannageID is unique, LiftID (LiftNaam) is not. "Aantal" shows the total amount of breakdowns with the active filters.

(5 rows with the same LiftID = 5)

I currently have a filter to get the total amount of items based on a time range.

Aantal = CALCULATE(
	COUNT(
		'F_DEPANNAGEANALYSE'[LiftID]
	);
	FILTER(
		'F_DEPANNAGEANALYSE';
		EARLIER(
			'F_DEPANNAGEANALYSE'[LiftID]
		) = 'F_DEPANNAGEANALYSE'[LiftID]
	);
	FILTER (
        'F_DEPANNAGEANALYSE',
            DATE(YEAR(NOW());MONTH(NOW())-6; DAY(NOW())) <= F_DEPANNAGEANALYSE[DepannageUitgevoerdDatumtijd]
            && F_DEPANNAGEANALYSE[DepannageOproepDatumtijd] <= DATE(YEAR(NOW());MONTH(NOW()); DAY(NOW()))
    ),
    all('F_DEPANNAGEANALYSE')
)

So the date range filter is not working on the last "Aantal" column. I currently hard coded the date range.
Is there any way to achieve this? I already tried a lot of suggestions on the forums (both measured and calculated table based).

Short: i am looking for an OUTER APPLY alternative in DAX (since i am not able to add the dynamic date filter in the SQL query).

Kind regards
Maxim

[amitchandak]

what is the date filter you want ?

[maximd]

It should replace this:

FILTER (
        'F_DEPANNAGEANALYSE',
            DATE(YEAR(NOW());MONTH(NOW())-6; DAY(NOW())) <= F_DEPANNAGEANALYSE[DepannageUitgevoerdDatumtijd]
            && F_DEPANNAGEANALYSE[DepannageOproepDatumtijd] <= DATE(YEAR(NOW());MONTH(NOW()); DAY(NOW()))
    ),

You can see the slicer in my screenshot. (Top left)
But is has no effect on the calculated column.

[V-lianl-msft]

Hi @maximd ,

Try to use ALLSELECTED instead of ALL.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[maximd]

This is what i'm trying to achieve, calculated column:

Aantal = CALCULATE(
	COUNT(
		'F_DEPANNAGEANALYSE'[LiftID]
	);
	FILTER(
		ALLSELECTED('F_DEPANNAGEANALYSE');
		EARLIER(
			'F_DEPANNAGEANALYSE'[LiftID]
		) = 'F_DEPANNAGEANALYSE'[LiftID]
	)
)

When I use the code above, my total count is not working anymore. It counts all breakdowns (depannages - period: all time).
It SHOULD show the total of breakdowns / LiftID (elevatorID) for that time period.

(Current example: 5 instead of 33)