Forum Discussion
Count function issue in DAX
Hi there,
I think the immediate issue is that the measure needs to iterate over combinations of date & timestamp, rather than just timestamp.
You could use GENERATE or CROSSJOIN to create a table with all date/timestamp combinations (within the filter context), since we can't rely on 'Puffer Vergleich' to give us all the combinations.
For example:
<20 Count = COUNTROWS ( FILTER ( GENERATE ( VALUES ( 'Main Date Table'[Datum] ), VALUES ( '20 min timestamp'[20 Min timestamps] ) ), [Puffer Füllstand test] = "<20%" ) )Does this give the expected result?
For an alternative way of handling this type of calculation, have a look at Dynamic Segmentation on DAX Patterns.
Regards,
Owen
OwenAuger
Thanks for your reply!
I only want to see the overall percentage count in ONE Donut. Maybe using the word "comparing" was misleading here. It's more an analysis of buffer fill levels. As you can see if I select two Buffers in the slicer "BESCHREIBUNG" for one day, it counts 71 values, however it should give me a count of 142 values. If you're wondering why 71 values, that's becuase I filtered the value for 00:00:00. Basically I want the count for every buffer selected over the time period selected.
The Last Value measure you created is still exactly the same:
Last Value final 20 minute stamp =
VAR OverallMaxDateTime =
CALCULATE (
MAXX ( 'Puffer Vergleich', 'Puffer Vergleich'[Datum] + 'Puffer Vergleich'[20 min timestamp]),
REMOVEFILTERS ()
)
VAR MaxDate =
MAX ( 'Main Date Table'[Datum] )
VAR MaxTime =
MAX ( '20 min timestamp'[20 Min timestamps])
VAR MaxDateTime =
MaxDate + MaxTime
RETURN
IF (
MaxDateTime <= OverallMaxDateTime,
VAR PastDateTime =
FILTER (
CALCULATETABLE (
SUMMARIZE (
'Puffer Vergleich',
'Main Date Table'[Datum],
'20 min timestamp'[20 Min timestamps]
),
'Main Date Table'[Datum] <= MaxDate,
REMOVEFILTERS ( '20 min timestamp' )
),
'Main Date Table'[Datum] + '20 min timestamp'[20 Min timestamps] <= MaxDateTime
)
VAR LatestDateTimeWithValue =
TOPN (
1,
PastDateTime,
'Main Date Table'[Datum] + '20 min timestamp'[20 Min timestamps]
)
VAR Result =
CALCULATE (
[Speicher Single Value Breaking ties 20 minute stamps],
LatestDateTimeWithValue,
REMOVEFILTERS ( '20 min timestamp' ) -- Time filters must be explicitly removed
)
RETURN
Result
)
Hi again frittle
Thanks for that, that makes sense 🙂
In that case, I would change GENERATE to CROSSJOIN, and join the 3 columns together (Date, Time and BESCHREIBUNG).
I'm assuming you are applying filters on MAXTABLE[BESCHREIBUNG], as MAXTABLE is effectively set up as a dimension table. Otherwise change to the appropriate column reference:
<20 Count =
COUNTROWS (
FILTER (
CROSSJOIN (
VALUES ( MAXTABLE[BESCHREIBUNG] ),
VALUES ( 'Main Date Table'[Datum] ),
VALUES ( '20 min timestamp'[20 Min timestamps] )
),
[Puffer Füllstand test] = "<20%"
)
)
Does this work as expected?
Regards,
Owen