Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to Solution.
@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.
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().
@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.
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?
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?
@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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |