Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
frittle
Helper II
Helper II

Count function issue in DAX

Dear Power Bi Community,

I'm rather new to Power Bi and facing a counting issue I can't quite explain to myself. Background: I am doing a buffer fill level analysis over time and want to find out in what percentage ranges based off of a buffers maximum capacity it is mainly in. I created a switch function that tells me if its fill level is <20%, 20-40%, 40-60%, 60-80% or >80%. I also have a large data set, so I created a measure that gives me the value closest to every 20 minute timestamp in 24 hours (if there is no value found it takes the last one), this measure is called "Last Value Final 20 Minute Stamp". This is important so that I have 72 data points every day. This is the dashboard I created, it counts the amount of occurances of each switch fucntion outcome as you can see using 5 countrows measures: 

frittle_0-1650799461599.png

Count rows measure example: 

<20 Count = COUNTROWS(FILTER(VALUES('20 min timestamp'[20 Min timestamps]),[Puffer Füllstand test] = "<20%"))

 

 

 

This works perfectly if I have one day selected only, if I do a multiple select for several days it only gives out the count for the last selected day and not every day's count added up: 


frittle_1-1650799680926.png

 

Is there anything I can change in my measures to get the desired result? I think my problem has something to do with the list of 72 values I am basing my count on.

 

here are my tables and connections:


frittle_2-1650799861033.png

 frittle_4-1650800066201.png

 

 frittle_3-1650800017727.png

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger 

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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. 

 

frittle_0-1650867359977.png

 



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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger 
Once again this was the soloution to my problem! intead of Maxtable[BESCHREIBUNG] I had to use the description column from my main table Puffer Vergleich[BESCHREIBUNG]. Again many thanks for taking the time and helping me out!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors