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

[dccosta82]

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!

[MFelix]

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]

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 · Accepted solution]

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.

[dccosta82]

Thank you!

The second method worked.

The first I didn't try because it's a calculated table and I have no access to the table transformations you show on the printscreens.