HASONEVALUE & ISINSCOPE add blank rows to Matrix

[rpiboy_1]

I've used either in the past to show blank or "n/a" or some such on text fields in Matrixes, when the value is unique per row. Trying to do it again, and I'm getting blank rows added to the matrix and I'm not sure why. Here is the data model:

Tbl Databases

Database.ID	Server Name	Database Name	Server Name Hierarchy (Server Name, Database Name)
1	US	DBOne	ServerName\DatabaseName
2	US	DBTwo	ServerName\DatabaseName
3	US	DBThree	ServerName\DatabaseName

Tbl Projects

Proj.ID	Database.ID	Project Name	Description
1	1	ProjNameOne	description here
2	2	ProjNameTwoA	null
3	2	ProjNameTwoB	description here
4	3	ProjectNameThree	null

Tables are related on the DB ID column. The matrix has the Server Name hierarchy and Project Name fields as Rows. I created a simple DAX for Description and I get the blank rows added at the top level, so there is blank for server\database\project name.

I've confirmed that I don't have any orphaned data on the Project table relative to the Database table. Any thoughts on what is happening here?

DAX code:

Description (returns blank) = 
VAR _isSglVal = HASONEVALUE('Projects'[Project Name])
VAR _description = MAX('Projects'[Description])

RETURN
IF(_isSglVal, _description, "false")

[johnt75 · Accepted solution]

Try using

Description measure =
SELECTEDVALUE ( 'Projects'[Description] )

That will return the description if only 1 value is visible in the filter context, otherwise it will return blank, and should not create any new rows on the matrix.

[rpiboy_1]

Reading 'the Italtian's' article about SELECTVALUE its interesting to note that they added a note that indicates there was some change pre March 2024, wonder if that change (whatever it was) also impacted the behavior of using HASONEVALUE in these types of situations, since SELECTEDVALUE is sugar syntax for a slightly more complicated operation.

[johnt75]

I don't think that there's any harm in using this approach, but the other functions have different use cases. ISINSCOPE is often confused with HASONEVALUE, people tend to treat them as if they are interchangeable when they're really not.

Both are more useful for logic switches than retrieving the value of the column being tested though. There's no point in writing

VAR A = IF( HASONEVALUE( 'Table'[Col A] ), SELECTEDVALUE( 'Table'[Col A] ) )

but

VAR A = IF( HASONEVALUE( 'Table'[Col A] ), SELECTEDVALUE( 'Table'[Col B] ) )

might make sense in some circumstances.

[rpiboy_1]

So, to that point about Logic switches. I have the not un-common circumstance that there is only one Project per Database (to the point that end users often mistakenly equate Database to Project). In this case HASONEVALUE is not useful at the Database level of the hierarchy when there is still only one Project in the scope. I was however able to use ISINSCOPE as a logic check to prevent the Description from propgating 'up' to the Database tier, even when the database to project relationship is 1:1.

Here is what I ended up with:

Description (returns blank) = 
VAR _DESCRIPTION = SELECTEDVALUE('Projects'[Description])
VAR _PROJCONTEXT = ISINSCOPE('Projects'[Name])
VAR _RESULT =
    IF(
        _PROJCONTEXT,
        _DESCRIPTION,
        BLANK()
    )
    
RETURN
_RESULT

[johnt75]

Yes, that's exactly what ISINSCOPE is for.

One thing to watch out for if you are doing multiple checks inside e.g. a SWITCH statement, you must check the levels in ascending order of the hierarchy, lowest first, as the higher levels remain in scope at lower levels. i.e. the below code would not work

Description =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Projects'[Database] ), SELECTEDVALUE ( 'Projects'[Database] ),
    ISINSCOPE ( 'Projects'[Name] ), SELECTEDVALUE ( 'Projects'[Name] )
)

because database is still in scope even when name is also in scope.

[rpiboy_1]

Make sense! Thanks for the tip.

[rpiboy_1]

Cool, that works, but why does it not get reccomended more often for solving the problem I was dealing with, google searches (and past experience) all lead to HASONEVALUE or ISINSCOPE. I guess the fundamental question is what are the caveats, risks or downsides to this approach?

[Anonymous]

Hi @rpiboy_1 , 

Please check the following measure:

Description (returns blank) = 
VAR _isSglVal = HASONEVALUE('Projects'[Project Name])
VAR _description = MAX('Projects'[Description])

RETURN 
IF(_isSglVal ,IF( _description<>BLANK(),_description, ""),BLANK())

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rpiboy_1]

That improvement with the check for blank helps, and makes sense. However in lieu of "" I had to use BLANK() as otherwise I was still getting an extra set of rows at the top of the matrix with no data. This also means that for all Projects to be visible in the visual I have to include some other column from Projects that forces it to show all rows as rows with a blank description were filtered out. Turning on 'show rows without data' resulted in the extra rows being added in the visual.

So this begs the question, of what exactly is going on in the DAX expression that is resulting in the extra rows being added, is it evidence of a problem in my model, and what further steps should I take to continue to investigate.

[rpiboy_1]

Attempting to do further analysis on what is happening in my model. If I take a simple table visual of the Projects Table, and a simple measure: HASONEVALUEmeasure = HASONEVALUE('Projects'[ID]) It generates an outcome where there is an additional row, and HASONEVALUEmeasure returns true. My dataset is small, so I'm certain the measure is producing an additional row, I just don't understand why. 'Project Name' is set to 'Keep Unique: true' and the ID column is set as the key column and set to 'Unique: true'. If I capture the Table's DAX query and run the query, I get the same result, extra row with all blanks, with the excepetion of 'HASONEVALUE' which reports true.

[rpiboy_1]

Correct Matrix, without the measure, so the 'first' description value is propegated up through the hierarchy.

[rpiboy_1]

I should re-phrase, it adds a blank row at the project level for each Database Name, and add blank rows for Server\Database, with all projects listed and adds rows under each hierachy for every project, even though the relationships look like the example above. Relationship from Databases to Projects is single direction, one to many.