ADDCOLUMNS: adding a new column with SELECTEDVALUE, get empty

[mnemolu]

This is the DAX used for creating the table:
--------------

result_table =

var users = VALUES('fact_table'[user_id])

var add_col =
ADDCOLUMNS(
users,
"selected_question" , CALCULATE(SELECTEDVALUE('dimension_table'[question],"No selection"))
)

return add_col
--------------



The selected_question in this table is always "No selection", while when I put SELECTEDVALUE('dimension_table'[question],"No selection") on a card visual, it shows the selected question.

I'm puzzled by the unexpected behavior and unsure of how to address it. Any insights on how to resolve this issue would be greatly valued. Thank you!

[amitchandak]

@mnemolu , Any filter or slicer selection can not be used in a calculated column. to have a slicer on measure consider dynamic segmentation

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4

[mnemolu]

I used the filter selection in another measure, where the filter selection is used within a calculated column.

And this measure works fine. Is it just the filter selection couldn't be used to generate a table?

This is the DAX for the measure:

----------------------

number_of_users_who_answered_selected_question =

var users = VALUES('fact_table'[user_id])

var add_col =
ADDCOLUMNS(
users,
"@X" , CALCULATE(COUNTROWS(
FILTER(
'fact_table',
'fact_table'[question]=SELECTEDVALUE('dimension_table'[question]))
))
)

var res =
FILTER(
add_col,
[@X] >0
)

var calc =
CALCULATE(DISTINCTCOUNT('fact_table'[user_id]),res)
return calc

--------------------

[v-yiruan-msft · Accepted solution]

Hi @mnemolu ,

The calculated column or calculated table will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column/table is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

That's why you got the value "No selection" in the field [selected_question]...

Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with special examples and screenshots? Later I will check if there is any workaround to achieve your requirement. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[mnemolu]

Thank you very much. I used a measure instead and have achieved the expected result.

I'm new to PowerBI and was not aware that a measure is calculated on the fly, while calculated column or calculated table isn't.