topic Re: SELECTEDVALUE inside table constructor in DAX Commands and Tips

SELECTEDVALUE inside table constructor

juan_pablo — Thu, 10 Oct 2024 17:42:41 GMT

Hi,

I have the following measure inside a filter context where the only selected value for column 'ParameterTable'[ItmNam] is "A" (in fact it's a parameter table):

CALCULATE( COUNTROWS('Table'), 'Table'[ItemName] IN {SELECTEDVALUE('ParameterTable'[ItmNam])} )

But the measure is not counting the rows in table 'Table' where ItemName = "A".

If I change the measure as follows, it works fine:

CALCULATE( COUNTROWS('Table'), 'Table'[ItemName] IN {"A"} )

Why SELECTEDVALUE doesn't works inside a table constreuctor?

Where is this limitation written or explained?

Note: I don't need a workaround (variables can solve it), I need the conseptual explanation or documentation plese.

Thank you!

marcorusso

Re: SELECTEDVALUE inside table constructor

Alex_Sawdo — Thu, 10 Oct 2024 18:08:40 GMT

If I recall correctly, SELECTEDVALUE() only will ever return a single value, and cannot return multiple values at once. What you should do is this:

CALCULATE( COUNTROWS( 'Table' ), 'Table'[Column1] IN ALLSELECTED(ParamTable[Column1]) )

This will properly count all of the selected values from the Param table, hence ALLSELECTED() rather than SELECTEDVALUE().

Re: SELECTEDVALUE inside table constructor

AlexisOlson — Thu, 10 Oct 2024 18:46:57 GMT

Alex_Sawdo, ALLSELECTED isn't necessarily what you want (though it might be in some cases).

I'd suggest one of the following instead:

CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ItemName] IN VALUES ( 'ParameterTable'[ItmNam] ) )

CALCULATE ( COUNTROWS ( 'Table' ), TREATAS ( VALUES ( 'ParameterTable'[ItmNam] ), 'Table'[ItemName] ) )

This doesn't really answer OP's question though.

juan_pablo, in my testing, the first measure does do what you're expecting if a single parameter value is selected.

Re: SELECTEDVALUE inside table constructor

juan_pablo — Thu, 10 Oct 2024 21:35:40 GMT

Hi AlexisOlson, thank you very much. It seems I oversimplified the example. There was a KEEPFILTERS involved. Attached is the original model, where you can see how differently these two measures behave:

Filter Table = CALCULATE( COUNTROWS('Item'), KEEPFILTERS('Category'[ItmsGrpNam] IN {SELECTEDVALUE('Categoria Obj 1'[ItmsGrpNam])}) ) vs Filter Table OK = CALCULATE( COUNTROWS('Item'), KEEPFILTERS('Category'[ItmsGrpNam] IN {"Viaka"}) )

Why do they behave differently?

SELECTEDVALUE.pbix

Re: SELECTEDVALUE inside table constructor

AlexisOlson — Fri, 11 Oct 2024 17:37:43 GMT

Thanks for the sample file.

There's something going on here that I don't understand and in my digging I found this related result that I'm leaving as a note for my future self:

EVALUATE SUMMARIZECOLUMNS ( Category[ItmsGrpNam], TREATAS ( { "Viaka" }, 'Categoria Obj 1'[ItmsGrpNam] ), TREATAS ( { "Activos" }, Category[ItmsGrpNam] ), "V1", /*Returns Viaka*/ CALCULATE ( MAX ( Category[ItmsGrpNam] ), KEEPFILTERS ( Category[ItmsGrpNam] = MAX ( 'Categoria Obj 1'[ItmsGrpNam] ) ) ), "V2", /*Returns Blank*/ CALCULATE ( MAX ( Category[ItmsGrpNam] ), KEEPFILTERS ( FILTER ( ALL ( Category[ItmsGrpNam] ), Category[ItmsGrpNam] = MAX ( 'Categoria Obj 1'[ItmsGrpNam] ) ) ) ), "V3", /*Returns Blank*/ CALCULATE ( MAX ( Category[ItmsGrpNam] ), KEEPFILTERS ( Category[ItmsGrpNam] = VALUES ( 'Categoria Obj 1'[ItmsGrpNam] ) ) ) )

I'm not sure why V1 doesn't match V2 or V3. marcorusso, can you help explain what's going on here?

Re: SELECTEDVALUE inside table constructor

marcorusso — Fri, 11 Oct 2024 20:13:02 GMT

AlexisOlson I got blank for the three columns. It could be that I'm using an updated version - try again with the October 2024 release when available, and let me know if you can still reproduce the issue.