COUNTROWS - Count NOTBLANK rows across two columns, only count once if both have value

[mmartinko]

I've tried calculate, groupby, and adding multiple measures, but I'm still not getting the result I need. If both columns have a value I only want to count it as 1. What am I missing?

Example: The count result I expect is 3.

Data1, Data2

1,blank

blank,1

1,1

blank,blank

[Ashish_Mathur]

Hi,

You can create a calculated column formula first

Test = 1*and(Data[Data1]<>blank(),Data[Data2]<>blank())

Next, create a measure

Measure = sum(Data[Test])

I have assumed that Data is the name of the Table.

Hope this helps. 

[mmartinko]

This looks like it could work, but is only bringing back a value of 1 in the calculated column if both columns are not blank. I need it to account for both not blank, or one or the other.

[Ashish_Mathur]

Hi,

Try this

Test = 1*not(and(Data[Data1]=blank(),Data[Data2]=blank()))

[mmartinko]

That did it! Thanks so much.

[Ashish_Mathur]

You are welcome.

[Nathaniel_C]

Hi @mmartinko ,
If I understand your question, please try this:

CountOne col = 

Switch (TRUE(),

NOT(ISBLANK(MAX(BlankTable[Column1]))),1,
NOT(ISBLANK(MAX(BlankTable[Column2]))),1

)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[mmartinko]

Thanks for the response. This just gives me a total of 1 though.

[vicky_ · Accepted solution]

Have you tried something like this: 

measure = COUNTROWS(FILTER('Table', NOT(ISBLANK('Table'[Data1])) || NOT(ISBLANK('Table'[Data2]))))

[mmartinko]

This appears to have worked as well and doesn't need a calculated column. Thanks!