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 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?

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.

     

4 Replies

    • v-karpurapud's avatar
      v-karpurapud
      Community Support
      1 year ago

      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's avatar
    SamWiseOwl
    Super User
    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