Specific Filter

[Anonymous]

Hello Community,

I'm new to Power BI and I have a question - I wish to filter out null values BUT ONLY for those product_ID s where there is no other string value than null. In other words, if a column contains null + other string when grouped by product_ID, then keep both null and other string values, if column grouped by the product_ID only cantains null, then I do not want to include such values. How to do that?

[Anonymous]

Hello @SamWiseOwl - brilliant, thank you so much!

[v-karpurapud]

Hi @Anonymous 

we wanted to kindly follow up to check if the solution provided by our community member  @SamWiseOwl  helps you? If it is worked for you please Accept it as Solution or let us know if you need any further assistance here? 

Your feedback is important to us, Looking forward to your response.

Thank You.

[SamWiseOwl]

Do the above if you want to create the column in the front end.

If you want to use the query editor:
Merge the two tables together

Expand the arrows and aggregate the column with the nulls.

Tick both Count (All) and Count (Not Blank)

This will return the number of rows with and without empties.

Now if one is blank and the other isn't you can filter it.

Both these rows are NULLs

[SamWiseOwl · Accepted solution]

Hi @Anonymous 
Where were you thinking of creating this?

As a calculated column you could do something like this:

Test value = If(

    BLANK() in CALCULATETABLE(Values(Sales[PaymentDate])) --Are there blanks in the other list

     && CALCULATE(DISTINCTCOUNT(Sales[PaymentDate])) = 1  --And there is only one variant in the data

     ,"Only Nulls", "Mixed")

Here for each shopping centre it is joined to a Sales table.

Each centre has multiple transactions.

If the Centre only has one transaction and its blank then return Only Nulls.

I could then use this as a filter on the visual.