Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a measure from a variable table

Good afternoon,

 

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

 

My direct query table contains this structure:

userssteps
1NEW
1NEW
1NEW
2OLD
2OLD
3OLD
4EQUAL
5EQUAL
6EQUAL

 

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")
usersnº values = NEW in steps
13
20
30
40
50
60

 

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

 

Thank you in advance 🙂

  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

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

     

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

     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

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

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      🤣🤣🤣I just want to scream 

       

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

       

      Thanks a lot.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        5 years ago

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

        SUMX ( tabla, IF ( [nº values] >= 3, 1, 0 ) )
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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 🙂