Forum Discussion

juan_pablo's avatar
juan_pablo
Helper V
1 year ago
Solved

SELECTEDVALUE inside table constructor

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('Ta...
"DAX "
"DAX help"
'DAX'
'Need help'
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

    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.

     

     

Alex_Sawdo's avatar
Alex_Sawdo
Resolver II
1 year ago

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().

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    1 year ago

    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.

     

     

    • juan_pablo's avatar
      juan_pablo
      Helper V
      1 year ago

      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

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        1 year ago

        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?