topic Re: Create a measure from a variable table in DAX Commands and Tips

Create a measure from a variable table

Anonymous — Tue, 11 May 2021 16:52:26 GMT

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 🙂

Re: Create a measure from a variable table

AlexisOlson — Tue, 11 May 2021 16:59:40 GMT

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

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

Re: Create a measure from a variable table

Jihwan_Kim — Tue, 11 May 2021 17:02:17 GMT

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

Re: Create a measure from a variable table

Anonymous — Tue, 11 May 2021 17:05:23 GMT

🤣🤣🤣I just want to scream

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

Thanks a lot.

Re: Create a measure from a variable table

Anonymous — Tue, 11 May 2021 17:07:15 GMT

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 🙂

Re: Create a measure from a variable table

AlexisOlson — Tue, 11 May 2021 17:41:01 GMT

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

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