Forum Discussion

dkc's avatar
dkc
Helper I
9 months ago
Solved

The CONTAINSROW function expects a table expression for argument '', but a string or numeric express

Hi, I am not sure how to correct this formula, I always get the error note The CONTAINSROW function expects a table expression for argument '', but a string or numeric expression was used.

CustomerCount_SelectedSalesReps =
VAR SelectedCustTable =
    IF(
        ISFILTERED('Hierarchy level Slicer'[HIERARCHY_LEVEL]),
        VALUES('Hierarchy level Slicer'[HIERARCHY_LEVEL]),
        ALL('Hierarchy level Slicer'[HIERARCHY_LEVEL])
    )

VAR ThisCustomer = MAX('FACTS_C4C Promos'[Hierarchy Level])

RETURN
CALCULATE(
    COUNTROWS('DIM_Customer Master'),
    FILTER(
        'DIM_Customer Master',
        'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL] = ThisCustomer
            &&
   
        CONTAINSROW(SelectedCustTable, 'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL])
    )
)
Need Help
  • danextian's avatar
    danextian
    9 months ago

    Hi dkc 

    You cannot use a condition to return a table or table expression. While SelectedCustTable doesn't return an error using this in another expression will cause an error.  Try this:

    VAR SelectedCustTable01 =
    VALUES ( 'Hierarchy level Slicer'[HIERARCHY_LEVEL] )
VAR SelectedCustTable02 =
    ALL ( 'Hierarchy level Slicer'[HIERARCHY_LEVEL] )
VAR ThisCustomer =
    MAX ( 'FACTS_C4C Promos'[Hierarchy Level] )
RETURN
    IF (
        ISFILTERED ( 'Hierarchy level Slicer'[HIERARCHY_LEVEL] ),
        CALCULATE (
            COUNTROWS ( 'DIM_Customer Master' ),
            FILTER (
                'DIM_Customer Master',
                'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL] = ThisCustomer
                    && CONTAINSROW (
                        SelectedCustTable01,
                        'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL]
                    )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'DIM_Customer Master' ),
            FILTER (
                'DIM_Customer Master',
                'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL] = ThisCustomer
                    && CONTAINSROW (
                        SelectedCustTable02,
                        'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL]
                    )
            )
        )
    )

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    9 months ago

    Hi,

    Could you share some data, explain the question and share the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • danextian's avatar
    danextian
    Super User
    9 months ago

    Hi dkc 

    You cannot use a condition to return a table or table expression. While SelectedCustTable doesn't return an error using this in another expression will cause an error.  Try this:

    VAR SelectedCustTable01 =
    VALUES ( 'Hierarchy level Slicer'[HIERARCHY_LEVEL] )
VAR SelectedCustTable02 =
    ALL ( 'Hierarchy level Slicer'[HIERARCHY_LEVEL] )
VAR ThisCustomer =
    MAX ( 'FACTS_C4C Promos'[Hierarchy Level] )
RETURN
    IF (
        ISFILTERED ( 'Hierarchy level Slicer'[HIERARCHY_LEVEL] ),
        CALCULATE (
            COUNTROWS ( 'DIM_Customer Master' ),
            FILTER (
                'DIM_Customer Master',
                'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL] = ThisCustomer
                    && CONTAINSROW (
                        SelectedCustTable01,
                        'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL]
                    )
            )
        ),
        CALCULATE (
            COUNTROWS ( 'DIM_Customer Master' ),
            FILTER (
                'DIM_Customer Master',
                'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL] = ThisCustomer
                    && CONTAINSROW (
                        SelectedCustTable02,
                        'DIM_Customer Master'[CUSTOMER_MASTER_DATA_HIERARCHY_LEVEL]
                    )
            )
        )
    )

     

  • dkc's avatar
    dkc
    Helper I
    9 months ago

    thank you, now it was working and I understood the logic