Forum Discussion

Walter_Mangy's avatar
Walter_Mangy
New Member
1 year ago
Solved

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

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 m...
  • johnt75's avatar
    johnt75
    1 year ago

    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.

johnt75's avatar
johnt75
Super User
1 year ago

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's avatar
Walter_Mangy
New Member
1 year ago

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