Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Costum with Condition

Hi Good day,

Im not sure if it possible to have an output i desired. 

I have a table Location 1 and Location 2, if in Location 1 dont have "XX000" then it will look on the Location 2 if both loacation dont have the first 5 format character then it copy the value in Location 1. 

 

XX000 could be NC000, NI000, NF000, NM011, NA000

 

 

Thank you in advance

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi AllanBerces 

    You can add calculated column with the formula :

    Output =
    var
    start_1= left('Table'[Location Name.l],5)
    var
    start_2= left('Table'[Location Name.2],5)
    RETURN
    if(start_1 IN({"NC000", "NI000", "NF000", "NM011", "NA000"}),'Table'[Location Name.l],
    if(start_2 IN({"NC000", "NI000", "NF000", "NM011", "NA000"}),'Table'[Location Name.2],'Table'[Location Name.l]))

    pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    Hi AllanBerces 

    You can add calculated column with the formula :

    Output =
    var
    start_1= left('Table'[Location Name.l],5)
    var
    start_2= left('Table'[Location Name.2],5)
    RETURN
    if(start_1 IN({"NC000", "NI000", "NF000", "NM011", "NA000"}),'Table'[Location Name.l],
    if(start_2 IN({"NC000", "NI000", "NF000", "NM011", "NA000"}),'Table'[Location Name.2],'Table'[Location Name.l]))

    pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Output CC =
VAR _list =
    SELECTCOLUMNS (
        { "NC000", "NI000", "NF000", "NM011", "NA000" },
        "@list", [Value]
    )
VAR _condition01 =
    ADDCOLUMNS (
        _list,
        "@condition", CONTAINSSTRING ( Data[location_name_1], MAX ( [@list] ) )
    )
VAR _condition02 =
    ADDCOLUMNS (
        _list,
        "@condition", CONTAINSSTRING ( Data[location_name_2], MAX ( [@list] ) )
    )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( FILTER ( _condition01, [@condition] = TRUE () ) ) <> 0, Data[location_name_1],
        COUNTROWS ( FILTER ( _condition02, [@condition] = TRUE () ) ) <> 0, Data[location_name_2],
        Data[location_name_1]
    )

     

    If you already have the table that tells about the list that you want, then you can put the column from the table inside the formula as VAR.