How VAR returns conditional list to filter column?

[Anonymous]

I want to pass a set of values to a filter which depends on a selected value.

Sales = 
VAR CollectionType = IF( SELECTEDVALUE(SalesPeriod[SalesPeriodCode]) = "A1"; {"A";"B";"C"}; {"A";"E";"F"})

VAR CollectionYear = IF( SELECTEDVALUE(SalesPeriod[SalesPeriodCode]) = "A1"; YEAR ( MAX ( 'Date'[Date] ) ) - 2; YEAR ( MAX ( 'Date'[Date] ) ) - 1)

RETURN
CALCULATE (
    SUM ( Sales[TurnoverinUnit] );
    FILTER (
        Collection;
        // Want to filter [Collection Type] with VAR CollectionType
        Collection[Collection Type] IN CollectionType
            && Collection[Collection Year]
                = FORMAT(CollectionYear; "####")
    )
)

CollectionYear works well but CollectionType is not recognized as a table when used to filter [Collection Type].

I tried something else:

Measure = 
VAR Test = {"A";"B";"C"}

RETURN
CALCULATE( SUM(Sales[TurnoverinUnit]); FILTER( Collection; Collection[Collection Type] IN Test))

Which works.

Do you know why this behaves like that?

Thanks

[Mariusz]

Hi @Anonymous 

If returns scalar value, it can not return a table, you can use PATH instead as per below

Table = 
VAR __test = IF( 1=2, "A|B|C", "A|E|F" )
RETURN 
SELECTCOLUMNS(
    GENERATESERIES( 1, PATHLENGTH( __test ), 1 ),
    "Value", PATHITEM(  __test, [Value], TEXT )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Sorry, I do not get it. I do not understand why [Value] will give position in:

PATHITEM(  __test; [Value]; TEXT )

Moreover, while trying to display the measure, I get the error message:

"A table of multiple values was supplied where a single value was expected"

Also to use it with my collection code, I will have to use this?

[CollectionType] IN [Table]

---

[Mariusz · Accepted solution]

Hi @Anonymous 

GENERATESERIES will return a table with Column Values between 1 and Count Of items in the path.
later this is converted to the actual path vale a,b,c if true or a,e,f if false.

My original DAX function was only representing the part of your code that was failing VAR CollectionType. 

Please see the below Measure adjusted to accommodate the changes.

Sales = 
VAR __collection = IF( SELECTEDVALUE( SalesPeriod[SalesPeriodCode] ) = "A1"; "A|B|C"; "A|E|F" )
VAR __values =  
    SELECTCOLUMNS(
        GENERATESERIES( 1; PATHLENGTH( __collection ); 1 );
        "Value"; PATHITEM(  __collection; [Value]; TEXT )
    )
VAR CollectionYear = IF( SELECTEDVALUE(SalesPeriod[SalesPeriodCode]) = "A1"; YEAR ( MAX ( 'Date'[Date] ) ) - 2; YEAR ( MAX ( 'Date'[Date] ) ) - 1)

RETURN
CALCULATE (
    SUM ( Sales[TurnoverinUnit] );
    FILTER (
        Collection;
        // Want to filter [Collection Type] with VAR CollectionType
        Collection[Collection Type] IN __values
            && Collection[Collection Year]
                = FORMAT(CollectionYear; "####")
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn