cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gluizqueiroz Regular Visitor
Regular Visitor

How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

I have the following measure:

 

 
Sold_On_Period = 
VAR Id_Group = SELECTEDVALUE(Table_Groups[Id_Group])
VAR MonthYearSelected = SELECTEDVALUE(Table_MonthYear[MonthYear])
VAR Id_GroupLine = CALCULATE(MAX(Products[Id_Group]))
RETURN
IF(Id_Group = Id_GroupLine;
    IF(MonthYearSelected IN VALUES(Products[MonthYear]); 1; 0);
    1
)


The measure works nice when user select just 1 MonthYear on slicer, but it not works when user select 2 or more MonthYear.

 

Is there any way to make the following verify with all selected values?

IF(MonthYearSelected IN VALUES(Products[MonthYear]); 1; 0);
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

@gluizqueiroz

 

Depends on what you need.

1. If you want the condition to yield true when at least one of the 

VALUES(Table_MonthYear[MonthYear]) is included in VALUES(Products[MonthYear]) then use:

 

Sold_On_Period =
VAR Id_Group =
    SELECTEDVALUE ( Table_Groups[Id_Group] )
VAR MonthYearSelected =
    DISTINCT ( Table_MonthYear[MonthYear] )
VAR Id_GroupLine =
    CALCULATE ( MAX ( Products[Id_Group] ) )
RETURN
    IF (
        Id_Group = Id_GroupLine;
        IF (
            COUNTROWS ( INTERSECT ( MonthYearSelected; VALUES ( Products[MonthYear] ) ) ) > 0;
            1;
            0
        );
        1
    )

 

2. If you want the condition to yield true when all of the 

VALUES(Table_MonthYear[MonthYear]) are included in VALUES(Products[MonthYear]) then use:

 

Sold_On_Period =
VAR Id_Group =
    SELECTEDVALUE ( Table_Groups[Id_Group] )
VAR MonthYearSelected =
    DISTINCT ( Table_MonthYear[MonthYear] )
VAR Id_GroupLine =
    CALCULATE ( MAX ( Products[Id_Group] ) )
RETURN
    IF (
        Id_Group = Id_GroupLine;
        IF (
            COUNTROWS ( INTERSECT ( MonthYearSelected; VALUES ( Products[MonthYear] ) ) )
                = COUNTROWS ( MonthYearSelected );
            1;
            0
        );
        1
    )

 

3. Should you need something else, you can modify the COUNTROWS, INTERSECT combination.

 

7 REPLIES 7
Super User
Super User

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

Hi @gluizqueiroz

 

SELECTEDVALUE( ) will by default return blank if more than one value is selected. You'd need to use VALUES() or DISTINCT() instead.

 

VAR MonthYearSelected = VALUES(Table_MonthYear[MonthYear])

 

gluizqueiroz Regular Visitor
Regular Visitor

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

Hi @AlB

I tried your solution but I receive a error message saying:
"A table of multiple values was supplied where a single value was expected"

Even with VALUES() or DISTINCT().

Super User
Super User

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

VAR MonthYearSelected = SELECTEDVALUE(Table_MonthYear[MonthYear])

will only return a single month or BLANK with this syntax, so you will not pass the multiple values this way

VALUES should work for you here

Super User
Super User

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

@gluizqueiroz

 

Depends on what you need.

1. If you want the condition to yield true when at least one of the 

VALUES(Table_MonthYear[MonthYear]) is included in VALUES(Products[MonthYear]) then use:

 

Sold_On_Period =
VAR Id_Group =
    SELECTEDVALUE ( Table_Groups[Id_Group] )
VAR MonthYearSelected =
    DISTINCT ( Table_MonthYear[MonthYear] )
VAR Id_GroupLine =
    CALCULATE ( MAX ( Products[Id_Group] ) )
RETURN
    IF (
        Id_Group = Id_GroupLine;
        IF (
            COUNTROWS ( INTERSECT ( MonthYearSelected; VALUES ( Products[MonthYear] ) ) ) > 0;
            1;
            0
        );
        1
    )

 

2. If you want the condition to yield true when all of the 

VALUES(Table_MonthYear[MonthYear]) are included in VALUES(Products[MonthYear]) then use:

 

Sold_On_Period =
VAR Id_Group =
    SELECTEDVALUE ( Table_Groups[Id_Group] )
VAR MonthYearSelected =
    DISTINCT ( Table_MonthYear[MonthYear] )
VAR Id_GroupLine =
    CALCULATE ( MAX ( Products[Id_Group] ) )
RETURN
    IF (
        Id_Group = Id_GroupLine;
        IF (
            COUNTROWS ( INTERSECT ( MonthYearSelected; VALUES ( Products[MonthYear] ) ) )
                = COUNTROWS ( MonthYearSelected );
            1;
            0
        );
        1
    )

 

3. Should you need something else, you can modify the COUNTROWS, INTERSECT combination.

 

gluizqueiroz Regular Visitor
Regular Visitor

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

Hey @AlB.

Your solution works nice man!
I wonder when I will have this knowlodge about DAX like you. 

Thanks again!

gluizqueiroz Regular Visitor
Regular Visitor

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

Hey @AlB.

Let me ask you.
I can use your measure to returns me the quantity of rows or it is necessary to create a new one?

I tried to adapt but it doesn't work. 

Thanks!

Community Support Team
Community Support Team

Re: How to capture all the selected values from a slicer and use it on a measure with IN VALUES()

@gluizqueiroz ,

 

Just try to use ALLSELECTED instead of VALUES.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.