cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Selected Value Blank

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.

3 REPLIES 3
jmweekes
Advocate I
Advocate I

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]
    )

 

Anonymous
Not applicable

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

sturlaws
Super User
Super User

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.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.