Add Row Context to DAX SWITCH Measure Power BI

Question

The original aim was to create 31 columns, that assess the DAY part of 3 dates and result a specific string in the particular column based on the IF statement. These 31 columns would then sit in a table alongside the title of the event and it would basically be a square Gantt chart for the 31 days. I could do this code wise in Mcode and advanced editor, but the the performance implications were a non-starter.

I can however build 31 measures that will appear like columns in the Matrix visualization on Power BI, this is fine. Except, Measures do not have a row context naturally.

I cannot for the life of me figure out how to add row context, none of the aggregators like SUM, SUMX, MIN, MAX work for a SWITCH statement as I'm not adding anything up, its a Boolean comparison. I understand that normally it'd just be built in a calculated column but its just not suitable.

Basically, I need a way to tie the below SWITCH into the Test_Table, ideally the [Title] column would be used but I just can't see how I can do this.

1 = 
    SWITCH(TRUE(),
        // Y-All
        'Test_Table'[Todays_Day_Number] = 1 &&
        'Test_Table'[Baseline_Date_Day_Number] = 1 &&
        'Test_Table'[Forecast_Date_Day_Number] = 1, "Y-All",
        // T-F
        'Test_Table'[Todays_Day_Number] = 1 &&
        'Test_Table'[Baseline_Date_Day_Number] <> 1 &&
        'Test_Table'[Forecast_Date_Day_Number] = 1, "T-F",

        // T-BL
        'Test_Table'[Todays_Day_Number] = 1 &&
        'Test_Table'[Baseline_Date_Day_Number] = 1 &&
        'Test_Table'[Forecast_Date_Day_Number] <> 1, "T-BL",

        // B  
        'Test_Table'[Todays_Day_Number] <> 1 &&
        'Test_Tabler'[Baseline_Date_Day_Number] = 1 &&
        'Test_Table'[Forecast_Date_Day_Number] <> 1, "B",

        // F
        'Test_Table'[Todays_Day_Number] <> 1 &&
        'Test_Table'[Baseline_Date_Day_Number] <> 1 &&
        'Test_Table'[Forecast_Date_Day_Number] = 1, "F",

        // T
        'Test_Table'[Todays_Day_Number] = 1 &&
        'Test_Table'[Baseline_Date_Day_Number] <> 1 &&
        'Test_Table'[Forecast_Date_Day_Number] <> 1, "T",
        "N"
)

As columns, this was multiples of conditions, 31 times for every row in the table, even filtered down it just did not help.

I'm probably missing something obvious, I know Gantt charts are available, but that's not the remit from the client.

Error is as follows:

A single value for column 'Todays_Day_Number' in table "Test_Table" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Calculated column for each day using If, then, else, and, each Reduced processed rows MAX MIN SUMX SUM ALL SELECTEDVALUE COUNTROWS list goes on.

Answer 1

What you are looking for is SELECTEDVALUE(...).

Don't create 31 measures, instead create one measure and use a table for the columns. For example, create a Calculated Table with:

Dim Day = 
  SELECTCOLUMNS(
    GENERATESERIES(1, 31),
    "Day", [Value]
  )

Use this new table/column for your Matrix Columns. Then have just this one Measure for the Matrix Values:

Day Measure =
  var colDay = SELECTEDVALUE('Dim Day'[Day], -1) // -1 for when not in scope
  var dayT = SELECTEDVALUE('Test_Table'[Todays_Day_Number])
  var dayB = SELECTEDVALUE('Test_Table'[Baseline_Date_Day_Number])
  var dayF = SELECTEDVALUE('Test_Table'[Forecast_Date_Day_Number])

  return SWITCH( TRUE(),
    // Y-All
    dayT = colDay && 
    dayB = colDay && 
    dayF = colDay, "Y-All",
    
    // T-F
    dayT = colDay && 
    dayB <> colDay && 
    dayF = colDay, "T-F",
    
    // T-BL
    dayT = colDay && 
    dayB = colDay && 
    dayF <> colDay, "T-BL",
    
    // B
    dayT <> colDay && 
    dayB = colDay && 
    dayF <> colDay, "B",
    
    // F
    dayT <> colDay && 
    dayB <> colDay && 
    dayF = colDay, "F",
    
    // T
    dayT = colDay && 
    dayB <> colDay && 
    dayF <> colDay, "T"
  )