topic Re: Selected Value Blank in DAX Commands and Tips

Selected Value Blank

Anonymous — Tue, 05 Nov 2019 11:39:46 GMT

Hi Guys, So I have two tables :
a. Name Master table
b.Data table

And a visual which contains all the names from the Name Master table

Name in the "Data table" can be in 6 different columns. The Name Master table is related to all 6 Columns
So my aim is to calculate an expression but for that I need to check in which of the 6 columns that name is present and then filter that "Data table" according in which column it has been found. Below is the Dax which I tried to write for returning the selected name but it is giving me blank at some places.

Selected Name= SELECTEDVALUE( Name_Master_List[Name])

Please help me guys I cant think anymore.

Re: Selected Value Blank

sturlaws — Tue, 05 Nov 2019 14:22:26 GMT

Hi Anonymous ,

here is one way of achieving what you want to do:

Measure = VAR _names = VALUES ( Master[name] ) VAR _c1 = CALCULATE ( SUM ( data[sales] ); FILTER ( data; data[Column1] IN _names ) ) VAR _c2 = CALCULATE ( SUM ( data[sales] ); FILTER ( data; data[Column2] IN _names ) ) VAR _c3 = CALCULATE ( SUM ( data[sales] ); FILTER ( data; data[Column3] IN _names ) ) VAR _c4 = CALCULATE ( SUM ( data[sales] ); FILTER ( data; data[Column4] IN _names ) ) RETURN _c1 + _c2 + _c3 + _c4

although with only 4 columns, but you get the picture. For this to work, you need to remove all the connections between Master and Data.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Re: Selected Value Blank

Anonymous — Wed, 06 Nov 2019 05:40:32 GMT

sturlawsCan we connect somewhere it will be easy to explain you the scenario

Re: Selected Value Blank

jmweekes — Wed, 03 Aug 2022 00:42:08 GMT

Thank you, sturlaws! This helped me solve a similar issue.

I had created a numeric measure using SUM() and a filter for [field] = SELECTEDVALUES([other field]).

Individual rows were returning the expected SUM() in my matrix visualization (since [other field] would have a single value within a single row's row context).

However, the Total row in the matrix was blank. I figured out this was due to the fact that the row context for the Total row would have multiple values for [other field] and, therefore, SELECTEDVALUE([other field]) in that row context would return null and, therefore, the SUM() was null.

After reading your post, I was able to update my formula to filter by IN VALUES([other field] and now my SUM(), SUMX() actually, is returning the correct, summed values for the Total row.

FilteredSum = /* Create a one-column table of Table1 key values for the current row context. * (This method, using VALUES() instead of SELECTEDVALUE() to get the value for the * current row/row context, is necessary in order to SUM() the measure when * there could be multiple "selected values" within the "Total" row context.) */ VAR t1KeyValues = VALUES('Table1'[T1_KEY]) RETURN SUMX( FILTER( 'Table2', 'Table2'[T2_KEY] IN t1KeyValues ), [T2_VALUE_TO_SUM] )