cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How VAR returns conditional list to filter column?

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: How VAR returns conditional list to filter column?

Hi @Grob2 

 

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

 

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: How VAR returns conditional list to filter column?

Hi @Grob2 

 

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

 

Highlighted
Helper I
Helper I

Re: How VAR returns conditional list to filter column?

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]

Highlighted
Super User IV
Super User IV

Re: How VAR returns conditional list to filter column?

Hi @Grob2 

 

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

 

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors