Dynamic lineage filter: “Depth” parameter + up/down‑stream toggle keeps throwing “expects a table”

[Walter_Mangy]

Data model

• Interaction mapping (fact) – two columns: Parent, Child
• Node (one‑column table) – list of every unique node; used as the starting‑point slicer
• Drilldown (numeric parameter 1→6) – how many generations to reveal
• Directionality(one-column table) – directionality of the analisys with three possibilities: descendants only, ancestors only, both directionalities

Visual goal

Sankey diagram that shows parent-child relationship. Three filters identify:

• Node to select for the analysis
• Drilldown depth of the analysis (e.g. show only direct parent and/or child, or extended parents and/or childs to increasing levels)
• Directionality allows to selectively show only parents, only childs or both directions

• Examples
  • Node = 4
  • Drilldown = 2 
  • Directionality = Both directionalities
    PowerBI should return  

• Node = 4
• Drilldown = 3
• Directionality = Use-case focused
  PowerBI should return

Sample data

Interaction mapping (fact)

Parent	Child
1	2
1	3
2	4
2	5
4	6
4	7
7	8
7	9
7	10
12	4

Node

Node
1
2
3
4
5
6
7
8
9
10
11
12

• Drilldown

Drilldown

1

2

3

4

5

6

Directionality

Drilldown

Use-cases focused (only descendants)

Data input focused (only ancestors)

Both directionalities

The DAX approach I took
Add to the Interaction mapping (fact table) a calculated measure named "Filter mapping" that flags with one all and only the rows that correspond to my selections in the above filters.
 
Filter mapping :=

/* Depth parameter */

VAR Depth = COALESCE ( SELECTEDVALUE ( Drilldown[Value] ), 1 )

/* Seed = nodes selected in the Filter slicer */

VAR Seed =

    SELECTCOLUMNS ( VALUES ( 'Filter'[Filter] ), "Node", 'Filter'[Filter] )

/* Empty = a 0‑row table shaped like Seed */

VAR Empty = FILTER ( Seed , FALSE () )

/* Build descendants Down1 … Down6 and ancestors Up1 … Up6

   (each IF() falls back to Empty so it always returns a table) */

-- ↓ FULL CODE OMITTED FOR BREVITY; structure is like:

VAR Down1 = Seed

VAR Down2 = IF ( Depth >= 2 ,

        SELECTCOLUMNS (

            FILTER ( ALL ( 'Interaction mapping' ),

                     'Interaction mapping'[Parent]

                         IN SELECTCOLUMNS ( Down1 , "Node", [Node] )

            ),

            "Node", 'Interaction mapping'[Child]

        ),

        Empty )

… (Down3‑6, Up1‑6 built the same way) …

VAR Downstream = DISTINCT ( UNION ( Down1, Down2, … Down6 ) )

VAR Upstream   = DISTINCT ( UNION ( Up1 , Up2 , … Up6  ) )

VAR Visible = UNION ( Downstream , Upstream )

/* Keep an edge if both ends are in Visible */

VAR CurParent = SELECTEDVALUE ( 'Interaction mapping'[Parent] )

VAR CurChild  = SELECTEDVALUE ( 'Interaction mapping'[Child] )

RETURN

IF (

    COUNTROWS ( FILTER ( Visible , [Node] = CurParent ) ) > 0 &&

    COUNTROWS ( FILTER ( Visible , [Node] = CurChild  ) ) > 0,

    1 , 0

)

I then set Filter mapping = 1 as a visual‑level filter on the Sankey.

The problem

Whenever I reference a previous generation inside the next FILTER … IN … statement, DAX flags the line:

SELECTCOLUMNS function expects a table expression for argument '', but a string or numeric expression was used.

Example of the offending piece (red squiggle under Down1):

FILTER (

    ALL ( 'Interaction mapping' ),

    'Interaction mapping'[Parent]

        IN SELECTCOLUMNS ( Down1 , "Node", [Node] )

)

I thought wrapping Down1 (a variable) in SELECTCOLUMNS(…, "Node", [Node]) would satisfy the “must be a table” rule, but the measure still fails to compile. If I hard‑code a small literal table – e.g. IN { "A", "B" } – it compiles, so the recursion seems to be the stumbling‑block.

What I’ve tried & still fails

• Adding explicit ELSE Empty to every IF so each var is always a table (no scalars).
• Replacing IN with TREATAS joins – same error sooner or later.
• Collapsing everything into one giant UNION inside a single variable – error just moves to that UNION.
  
  

What I’m looking for

• Why does DAX still see a scalar where I think I’m handing it a table variable?

Any ideas, corrections, or working examples would be hugely appreciated!

[johnt75 · Accepted solution]

Its the same problem - SWITCH returns a scalar value and you're trying to return a table. I think you need to combine the SWITCH statement with the check for CONTAINS, e.g.

VAR Result =
    SWITCH (
        SelectedDirection,
        "Use-cases focused",
            CONTAINS ( Upstream, [Node], CurParent )
                && CONTAINS ( Upstream, [Node], CurChild ),
        "Data input focused",
            CONTAINS ( Downstream, [Node], CurParent )
                && CONTAINS ( Downstream, [Node], CurChild ),
        "Both directionalities",
            CONTAINS ( UNION ( Downstream, Upstream ), [Node], CurParent )
                && CONTAINS ( UNION ( Downstream, Upstream ), [Node], CurChild ),
        CONTAINS ( UNION ( Downstream, Upstream ), [Node], CurParent )
            && CONTAINS ( UNION ( Downstream, Upstream ), [Node], CurChild ) // Default case
    )
RETURN
    Result

If you delete the Visibe variable and replace the RETURN statement with the above I think that should work.

[Walter_Mangy]

Thanks a lot Johnt75, support was prompt and relevant. Incredible work, all the best!

[johnt75]

I think the problem is that you're returning a table from within the IF function, but IF can only return scalar values. You could try reworking your code to something like

VAR Down2 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 2
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( Down1, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

adding the test from the IF function into the FILTER conditions.

[Walter_Mangy]

That was super useful, thanks for the prompt support! Reverting the function made it work.
I now though have the same error when I try to perform the very last task, which is getting the Sankey diagram to display alternatevely only the right part of the selection (the parents ancestors), only the left part of the selection (the child descendants) or both directionalities. To do so, I have included a one-column table in the model

Directionality

Drilldown

Use-cases focused (only descendants)

Data input focused (only ancestors)

Both directionalities

In the measure I have added a new variable
VAR SelectedDirection =

    SELECTEDVALUE (

        Directionality[Directionality],

        "Both directionalities" // Default to both if nothing is selected

    )

When I try to use this selection in the SWITCH function

VAR Visibe =

    SWITCH (

        SelectedDirection,

        "Use-cases focused", Upstream,

        "Data input focused", Downstream,

        "Both directionalities", UNION ( Downstream, Upstream ),

        UNION ( Downstream, Upstream ) // Default case

    )

I get back the same error message down the road 

The CONTAINS function expects a table expression for argument '', but a string or numeric expression was used.

I also attach the full measure, even though it is pretty repetitive

Filter mapping3 = 
/* 1. New Parameter for Directionality */
VAR SelectedDirection =
    SELECTEDVALUE (
        Directionality[Directionality],
        "Both directionalities" // Default to both if nothing is selected
    )

/* 2. Build seed sets */
VAR SelParents = VALUES ( 'Nodes'[Nodes] )
VAR SelChildren = VALUES ( 'Nodes'[Nodes])

VAR ParentSeed =
    SELECTCOLUMNS ( SelParents , "Node", 'Nodes'[Nodes] )
VAR ChildSeed =
    SELECTCOLUMNS ( SelChildren, "Node", 'Nodes'[Nodes] )

VAR Depth = COALESCE ( SELECTEDVALUE ( Drilldown[Drilldown depth] ), 1 )

/* 3a. Downstream from selected parents */
VAR DownGen1 =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Interaction mapping' ),
            'Interaction mapping'[Parent] IN ParentSeed ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen2 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 2
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen1, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen3 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 3
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen2, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen4 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 4
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen3, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen5 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 5
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen4, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen6 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 6
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen5, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen7 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 7
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen6, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen8 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 8
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen7, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen9 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 9
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen8, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR DownGen10 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 10
                && 'Interaction mapping'[Parent] IN SELECTCOLUMNS ( DownGen9, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Child]
    )

VAR Downstream = UNION ( ParentSeed, DownGen1, DownGen2,DownGen3, DownGen4,DownGen5, DownGen6,DownGen7, DownGen8,DownGen9, DownGen10)

/* 3b. Upstream from selected children */
VAR UpGen1 =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Interaction mapping' ),
            'Interaction mapping'[Child] IN ChildSeed ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen2 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 2
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen1, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen3 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 3
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen2, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen4 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 4
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen3, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen5 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 5
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen4, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen6 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 6
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen5, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen7 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 7
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen6, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen8 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 8
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen7, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen9 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 9
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen8, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR UpGen10 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Interaction mapping' ),
            Depth >= 10
                && 'Interaction mapping'[Child] IN SELECTCOLUMNS ( UpGen9, "Node", [Node] )
        ),
        "Node", 'Interaction mapping'[Parent]
    )

VAR Upstream = UNION ( ChildSeed, UpGen1, UpGen2,UpGen3, UpGen4,UpGen5, UpGen6,UpGen7, UpGen8,UpGen9, UpGen10)

/*VAR Visibe = UNION ( Downstream, Upstream )*/

VAR Visibe = 
    SWITCH (
        SelectedDirection,
        "Use-cases focused", Upstream,
        "Data input focused", Downstream,
        "Both directionalities", UNION ( Downstream, Upstream ),
        UNION ( Downstream, Upstream ) // Default case
    )


/*-------------------------------------------
  6. Current row’s endpoints
-------------------------------------------*/
VAR CurParent = SELECTEDVALUE ( 'Interaction mapping'[Parent] )
VAR CurChild  = SELECTEDVALUE ( 'Interaction mapping'[Child] )

/*-------------------------------------------
  7. Flag: 1 = keep the edge, 0 = hide it
-------------------------------------------*/
RETURN
IF (
        CONTAINS ( Visibe, [Node], CurParent )
    &&  CONTAINS ( Visibe, [Node], CurChild  ),
    1,
    0
)

Thanks for the time you will dedicate on this