Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Specific Filter

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 c...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    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.

     

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

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