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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Measure: count if value does not appear in another column

Hi!

 

I cannot seem to figure out how to create a measure that would calculate the number of times a value appears in another column.

 

My tables and the measure result I would like:

Column1 - Column 2  - Measure (total 3)

1               4                0

2               5                0

3               5               0

4                                1

5                                2

6                                0

7

 

 

Seems simple, but how to solve this in a measure?

Thanks in advance for any thoughts!

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

Unfortunately, I don't know what is the structure of your dataset, but it worked ok when applied to the sample you have provided. 

All() - Removes all filters from a current filter context this allows the measure to count occurrences of  5 that are in different rows.

image.png

VAR x = VALUES( 'Table'[Column1] ) -- this part selects distinct value for column1 in each give row and all values for total
RETURN 
CALCULATE(
    COUNTROWS( 'Table' ), --  count rows in a table in a filter context created by CALCULATE 
    ALL(), - removes all filters 
    TREATAS( x, 'Table'[Column2] ) -- filters table where column2 = column1
) + 0

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

You can try the below.

 

Measure = 
VAR x = VALUES( 'Table'[Column1] )
RETURN 
CALCULATE(
    COUNTROWS( 'Table' ),
    ALL(),
    TREATAS( x, 'Table'[Column2] )
) + 0

 

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

Thanx!

 

It works well on the row level, however the measure total does not give the sum on the row values. What is the logic for the measure total calculation?

 

Secondly, could you please tell me, why the ALL() is needed in there (why without it it does nnot work on the row level?

 

Thanks in advance!

Hi @Anonymous 

 

Unfortunately, I don't know what is the structure of your dataset, but it worked ok when applied to the sample you have provided. 

All() - Removes all filters from a current filter context this allows the measure to count occurrences of  5 that are in different rows.

image.png

VAR x = VALUES( 'Table'[Column1] ) -- this part selects distinct value for column1 in each give row and all values for total
RETURN 
CALCULATE(
    COUNTROWS( 'Table' ), --  count rows in a table in a filter context created by CALCULATE 
    ALL(), - removes all filters 
    TREATAS( x, 'Table'[Column2] ) -- filters table where column2 = column1
) + 0

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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