Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.
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)
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |