Forum Discussion

dccosta82's avatar
dccosta82
Frequent Visitor
3 years ago
Solved

Calculated column that checks whether a value in another column exists based on third value

Hi,

 

To simplify my issue:

 

Let's say I have a table with Route, Month and Location.

I want to be able to create calculated columns with specific month designations that check if that route is present for each month based on the Route field, kind of like pivoting the data without actually pivoting it.

 

Example of base:

 

Route, Month, Location


Lisbon-Porto, Jan, Lisbon
Lisbon-Porto, Feb, Lisbon
Lisbon-Porto, Mar, Lisbon
Lisbon-Porto, July, Lisbon

Porto-Lisbon, Jan, Porto


And turn it into:

 

Route, Month, Location, Jan?, Feb?, Mar?, Apr?, May?, June?, July?


Lisbon-Porto, Jan, Lisbon, YES, YES, YES, NO, NO, YES

Lisbon-Porto, Feb, Lisbon, YES, YES, YES, NO, NO, YES
Lisbon-Porto, Mar, Lisbon, YES, YES, YES, NO, NO, YES

Lisbon-Porto, July, Lisbon, YES, YES, YES, NO, NO, YES

Porto-Lisbon, Jan, Porto, YES, NO, NO, NO, NO, NO

 

Thanks in advance for all the help!

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    You want to add a column for each month on your dataset is that it?

     

    In this case you have two options:

    Power Query

     

    • Group your table by route and location
      • Select all rows in the option of aggregation

    • Add a custom step that create a list based on month

     

    Table.ToList (Table.SelectColumns ( [TableList], "Month"))

     

    • Do merge with the Source step by location and route

    • Expand the list from the table
    • Add a custom step with the following code:
    = List.Accumulate({"Jan", "Feb", "Mar", "Apr", "May", "June", "July","Aug", "Sep", "Oct", "Nov", "Dec"}, 
                                #"Expanded Added Custom1", (state, current) => 
                                Table.AddColumn(state, current, each if List.Contains( [Added Custom1.Custom], current) then "YES" else "NO"))

     

    Result below:

     

    Calculated Columns in DAX

     

    For each month add the following column sintax:

    July =
VAR routeselection = 'Table (2)'[Route]
VAR locationselection = 'Table (2)'[Location]
RETURN
    IF (
        "July"
            IN SELECTCOLUMNS (
                FILTER (
                    ALL ( 'Table (2)'[Route], 'Table (2)'[Month] ),
                    'Table (2)'[Route] = routeselection
                        && 'Table (2)'[Location] = locationselection
                ),
                "Months", 'Table (2)'[Month]
            ),
        "YES",
        "NO"
    )

     

    You need to replace the name of the Month and the text after the IF

    See attach PBIX file.

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    HI dccosta82 ,

     

    Create a table with the months then add the following measure:

    Check Month = IF(CALCULATE(COUNTROWS(Routes), Routes[Month] in  VALUES(Months[Month]))> 0 , "YES", "NO")

     

     

    • dccosta82's avatar
      dccosta82
      Frequent Visitor
      3 years ago

      Thank you for the quick reply Miguel!

      I would however really like to do it in a calculated column within that same table.

       

      Can I do it?

      • MFelix's avatar
        MFelix
        Super User
        3 years ago

        You want to add a column for each month on your dataset is that it?

         

        In this case you have two options:

        Power Query

         

        • Group your table by route and location
          • Select all rows in the option of aggregation

        • Add a custom step that create a list based on month

         

        Table.ToList (Table.SelectColumns ( [TableList], "Month"))

         

        • Do merge with the Source step by location and route

        • Expand the list from the table
        • Add a custom step with the following code:
        = List.Accumulate({"Jan", "Feb", "Mar", "Apr", "May", "June", "July","Aug", "Sep", "Oct", "Nov", "Dec"}, 
                                #"Expanded Added Custom1", (state, current) => 
                                Table.AddColumn(state, current, each if List.Contains( [Added Custom1.Custom], current) then "YES" else "NO"))

         

        Result below:

         

        Calculated Columns in DAX

         

        For each month add the following column sintax:

        July =
VAR routeselection = 'Table (2)'[Route]
VAR locationselection = 'Table (2)'[Location]
RETURN
    IF (
        "July"
            IN SELECTCOLUMNS (
                FILTER (
                    ALL ( 'Table (2)'[Route], 'Table (2)'[Month] ),
                    'Table (2)'[Route] = routeselection
                        && 'Table (2)'[Location] = locationselection
                ),
                "Months", 'Table (2)'[Month]
            ),
        "YES",
        "NO"
    )

         

        You need to replace the name of the Month and the text after the IF

        See attach PBIX file.