Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Resident Rockstar
Resident Rockstar

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors