I'm relatively new to Power BI, I wanted to know if there is a way to have a column or a way to track if a specific column goes from 'Y' to 'N' or from 'N' to 'Y'.

For Example:

If Bob the Builder called up to say he would like to receive Marketing, we'd need to change his 'N' to a 'Y'. Or if Barney or someone else wanted to opt out, we'd have to change their 'Y' to a 'N'. Yes, there is a way to track these changes. I do build the code for this data in an SQL query first and then import it to Power BI.

Yes, you can track changes in a specific column in Power BI by using historical data and creating calculated columns or measures to identify when a specific column goes from 'Y' to 'N' or 'N' to 'Y'. You can achieve this by using DAX (Data Analysis Expressions) to compare current and previous values of the column and create flags or indicators for the changes.

For example, you can create a calculated column to compare the current and previous values of the specific column and then determine if there has been a change from 'Y' to 'N' or 'N' to 'Y'. Alternatively, you can use measures to calculate the count or frequency of these changes over time.

1. Creating a Calculated Column in Power BI with DAX

To compare current and previous values of a specific column and identify changes from 'Y' to 'N' or 'N' to 'Y', you can use DAX. For example:

ChangeFlag = 
VAR CurrentValue = 'TableName'[YourColumn]
VAR PreviousValue = 
    CALCULATE(
        MAX('TableName'[YourColumn]),
        FILTER(
            'TableName',
            'TableName'[DateColumn] = EARLIER('TableName'[DateColumn]) - 1
        )
    )
RETURN
    IF(CurrentValue <> PreviousValue && (CurrentValue = "Y" || CurrentValue = "N"), 1, 0)

2.Using Measures to Calculate Change Counts:

If you want to calculate the number of changes, you can use a measure:

ChangeCount = 
SUMX(
    'TableName',
    IF('TableName'[ChangeFlag] = 1, 1, 0)
)

Since you mentioned that you build the code for the data in SQLquery first and then import it into Power BI, you can also consider implementing logic in your SQL query to identify and flag these changes before importing the data into Power BI.

3. Implementing Logic in SQL Query

You can also implement similar logic in SQL to identify changes before importing data into Power BI. For example:

SELECT 
    *,
    CASE 
        WHEN LAG(YourColumn) OVER (ORDER BY DateColumn) = 'Y' AND YourColumn = 'N' THEN 'Y to N'
        WHEN LAG(YourColumn) OVER (ORDER BY DateColumn) = 'N' AND YourColumn = 'Y' THEN 'N to Y'
        ELSE NULL
    END AS ChangeFlag
FROM 
    YourTable

Overall, tracking changes from 'Y' to 'N' or 'N' to 'Y' in a specific column is achievable in Power BI through the use of calculated columns, measures, and potentially implementing logic in the SQL query.