Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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);
Solved! Go to Solution.
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.
Just try to use ALLSELECTED instead of 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
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])
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().
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.
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!
Hey @AlB.
Your solution works nice man!
I wonder when I will have this knowlodge about DAX like you.
Thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |