locked
how do you hard code a table or column (for testing) RRS feed

  • Question

  • Hello,

    For example, 

    If i have this:  COUNTROWS( VALUES( 'Table'[fruit] ) )

    but I just want to test it by replacing the VALUES() with what would be a column of values.... something like:

      COUNTROWS( "apple", "pear" )    <=obviously doesn't work :)

    How would I do that?

    (I looked through the DAX language reference and didn't see something like this.....)

    Thanks,

    Al

    Monday, September 26, 2016 11:55 AM

Answers

  • The most convenient way to do this is to have a separate table in your model for testing.

    If you really want to do it in DAX, there's no function to hard code a table, but there is a function to hard code a row (being a one-row table), which is called ROW.

    Depending on the version of Power Pivot you're using (no Excel 2013), you could use the UNION function to append multiple rows into one table. E.g.

    TestResult = 
    VAR rowdata = ROW("Value", 1)
    RETURN
    SUMX(
       UNION(rowdata, rowdata, rowdata, rowdata),
       [Value]
    )
    which returns 4.

    Monday, September 26, 2016 2:39 PM
    Answerer

All replies

  • The most convenient way to do this is to have a separate table in your model for testing.

    If you really want to do it in DAX, there's no function to hard code a table, but there is a function to hard code a row (being a one-row table), which is called ROW.

    Depending on the version of Power Pivot you're using (no Excel 2013), you could use the UNION function to append multiple rows into one table. E.g.

    TestResult = 
    VAR rowdata = ROW("Value", 1)
    RETURN
    SUMX(
       UNION(rowdata, rowdata, rowdata, rowdata),
       [Value]
    )
    which returns 4.

    Monday, September 26, 2016 2:39 PM
    Answerer
  • Yeah, I agree with you regarding the separate table....

    ....but the ROW function works OK.  

    Dank U!

    Alex

    Tuesday, September 27, 2016 7:15 AM