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
Hello Owen,
This is exactly what I was looking for! Great! Would it also be possible to compare different buffers by multiple selecting "BESCHREIBUNG" like this? Right now it only works for one selected description
That's good to hear 🙂
As far as comparing buffers, that should be possible. It's just a question of how you want to arrange the values for each selected buffer in a visual, which may influence how the measure(s) should be written.
Roughly how did you want to visualize that?
Also, could you post the code for the current version of the [Last Value final 20 minute stamp] measure? (I'm assuming it's similar to the one from the earlier post).
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