Search code examples
listfunctionpowerbicontainsexists

Power BI function that checks if multiple specified values (numbers) exist in a column

Is there a function in Power BI that can check whether a list of specified values (numbers) exists in a column?

For example, in the image below, I have a column with some values and another one with 0 and 1s. You can see that some values are marked with 1 and some with 0. In order to do this, I used IF function, but this is just too cumbersome.

I am looking for a formula that can check if the values from a list like {XXXX, XXXX, XXXX, etc} exist in a column and that can easily be edited when I need to add other values.

Thank you and have a good day! Best, Denis

enter image description here

Solution

  • You can do that by adding a custom column for example. If we assume your table is named Table and first column is named Value, then add a custom column like this:

    enter image description here

    Where the list contains all the values of interest. This will give you a boolean column Flag:

    enter image description here

    If you want an integer column with 0 and 1 values, then change the column to something like this:

    = if (List.Contains({"5006", "4905"}, [Value])) then 1 else 0

    enter image description here