cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

countrows ignoring filter

hello,

 

trying to compare a count of multiple transaction vs single transactions.

i have a table with 2 columns, first one is GATEVISITNUMBER, second column is NBR

 

GATEVISITNUMBER is the primary key, and NBR is a unique number that represent different transaction associated with GATEVISITNUMBERS

for example:

 

GATEVISITNUMBER     NBR

1                                   1

1                                   2

2                                   3

3                                   4

4                                   5

4                                   6

5                                   7

 

my measures:

count nbr measure = count(N4_TruckTransactions[NBR])
single transaction = CALCULATE(count(N4_TruckTransactions[GATEVISITNUMBER]),filter(N4_TruckTransactions,[count nbr measure]=1))
double transaction = CALCULATE(count(N4_TruckTransactions[GATEVISITNUMBER]),filter(N4_TruckTransactions,[count nbr measure]=2))
 
but however the Single measure is returning all rows
and Double measure returning blanks.
 
what am i doing wrong? or if there is a better way to do this?
 
thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Please try this small change instead.  There is something called context transition that happens with you use a measure in your FILTER evaluation.  It is a good thing but, since you are filtering the whole table, a filter is created on both columns and every row returns 1.  That is what your first one gets all rows and your second one gets none.  To get your desired result, you just want to filter the one column.

 

single transaction =
COUNTROWS(
    FILTER (
        DISTINCT ( N4_TruckTransactions[GATEVISITNUMBER] ),
        [count nbr measure] = 1
    )
)

 

And then use same but with = 2 for second measure

 

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

Please try this small change instead.  There is something called context transition that happens with you use a measure in your FILTER evaluation.  It is a good thing but, since you are filtering the whole table, a filter is created on both columns and every row returns 1.  That is what your first one gets all rows and your second one gets none.  To get your desired result, you just want to filter the one column.

 

single transaction =
COUNTROWS(
    FILTER (
        DISTINCT ( N4_TruckTransactions[GATEVISITNUMBER] ),
        [count nbr measure] = 1
    )
)

 

And then use same but with = 2 for second measure

 

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted

thanks Pat ,

 it works!

😀👍

but can you explain a little more on 
"a filter is created on both columns and every row returns"

still don't understand why first measure returns all rows and second returns blanks.

 

i first thought calculate() and filter() always goes together, but apperently not.

and when do I need to use the calculate() ?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors