Create a measure from a variable table

[Anonymous]

Good afternoon,

I am not able to complete one of my needed measures. 

My direct query table contains this structure:

users	steps
1	NEW
1	NEW
1	NEW
2	OLD
2	OLD
3	OLD
4	EQUAL
5	EQUAL
6	EQUAL

I need a measure to distincount the number of users that has 3 times "NEW" in the "step" column. 

I am not able to create a calculated column because I am working with Direct Query. 

Right now, the furthest that I have got is creating a variable table where seems like this:

VAR tabla = 

ADDCOLUMNS(VALUES(table[users]),"nº values", CALCULATE(COUNT(table[steps]),segment_flatten[event] = "NEW")

users	nº values = NEW in steps
1	3
2	0
3	0
4	0
5	0
6	0

I would like to have this measure to be equal = 1 

Thank you in advance 🙂

[Jihwan_Kim]

Hi, @Anonymous 

Please try the below for creating a new table.

VAR Tables =
ADDCOLUMNS (
VALUES ( 'Table'[users] ),
"@values",
CALCULATE ( DISTINCTCOUNT ( 'Table'[users] ), 'Table'[steps] = "NEW" ) + 0
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thanks for your quick answer Jihwan.

The thing here is that I didn´t want to create a new table, just a variable table.

Thank you anyways 🙂

[AlexisOlson · Accepted solution]

Count the number of rows where "nº values" is three or greater.

COUNTROWS ( FILTER ( tabla, [nº values] >= 3 ) )

[Anonymous]

🤣🤣🤣I just want to scream 

I've been toying with several options, I didn't think about this possibility. 

Thanks a lot.

[AlexisOlson]

There are plenty of other ways to do it too. E.g.

SUMX ( tabla, IF ( [nº values] >= 3, 1, 0 ) )