Power bi measure ISBLANK AND NOT ISBLANK

[SPLeon]

Hi there,

I'm just started with a report of a file server scan. I have a excel file with all the files and folders of a file share.
The column of the path (file path), I already did split it up into seperate columns:

- Drive
- Root folder
- Subfolder
- Subfolder 2
- etc

Also we have a column for the owner of the folder. Now what I want to do is to give me only the owner of the first subfolder (column subfolder)

So I created the following measure:

Owner = 

FILTER('Scan results',
    IF(AND(NOT ISBLANK('Scan results'[Sub folder]),ISBLANK('Scan results'[Sub folder 2])),
        'Scan results'[Owner])
        
        

When I want to create this measure I get the following error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Can someone assist me with this?

[HotChilli]

The error is because FILTER will reduce the rows of your table according to a condition e.g FILTER('cars', 'cars'[colour] = "blue").

Your DAX creates a filtered table and tries to assign that to a measure (which is a scalar value). So that won't work.

If you provide some sample data and desired outcome, someone will help

[SPLeon]

Ok, I understand. But I dont see any other options to create my measure.

Hopefully my description of the issue will let someone help me.

I only want to retreive information from a specific column (owner column), when sub folder column is filled. But deeper levels in the folder hierarchy I dont want to know the owners (for now). With this information we can contact the owners of a specific folder level. 

[v-cherch-msft · Accepted solution]

Hi @SPLeon

You may refer to below measure. First, replacing all the blank values to null and then you may create a measure as requested.

Measure =
IF (
    NOT ( ISBLANK ( MAX ( 'Scan results'[Sub folder] ) ) )
        && ISBLANK ( MAX ( 'Scan results'[Sub folder 2] ) ),
    MAX ( 'Scan results'[Owner] )
)

 

Regards,

Cherie

[SPLeon]

Hello @v-cherch-msft,

Thanks for the reply. You did help me a lot by using the MAX function.
I executed your query.

Because I used a measure table, I couldn't reference to another table. That's why I used the filter.
Now by using the MAX I can reference to it and it's giving some result, but not the correct ones.

Some rows are still empty. I don't know why. In query editor I see no blank values, only nulls on the sub folders.

[SPLeon]

Never mind, your solution is correct, but the way I made is, is not correct.
I made matrix tabel

Name subfolder, count subfolder 2, count subfolder 3, owner

As de count of subfolder 2 and the rest is filled, i'm not getting the right result.

I have to make some other kind of formula to check the owner of the subfolder and not the rest.