Forum Discussion

JavierLopezFALP's avatar
JavierLopezFALP
Frequent Visitor
1 year ago
Solved

DAX Calculated Column with "variable tables" as variables.

Greetings

 

I'm attempting to create a calculated column that brings out a result based on the rowscount form another table. The relation is ONLY the id of each individual. The following illustrates the relationship between 2 appointment tables, each of a diferente service.

 

 

Basically, if a person with an appointment on the left table has an apointment on the right table (along some other variables related to dates and service details), the result is either YES or NO, depending on whether the rowcount of a variable, filtered AND related table is more than 0, or not.

column = 
--[state] can adopt values 1 to 5, and there are blank values. 

var table1 = filter(relatedtable(table2), not(isblank([state])))
var table2 = filter(relatedtable(table2), [state] in {1, 2})
var table = if(countrows(table2) < 1, table1, table2)

var conditioned_tableA =
filter
(table, ## additional conditions related to date and other variables.##)

var countB = countrows(conditioned_tableA)

var conditioned_tableB =
filter
(table, ## different conditons from A ##)

var countB = countrows(conditioned_tableB)

return

switch
(   true()
   , countA > 0, "YES"
   , countB > 0, "NO"
   , "YES"
)

 

However, I'm getting the error  

the expression refers to multiple columns. multiple columns cannot be converted to a scalar value

 

Of course, I know that I can't treat a table as a single value, or viceversa. But that is not what I want. The basis table can either be table1 or table2 depending on wether table2 holds any records. But my end result still is just the count of rows.

 

¿Any ideas?

@Dax
@Power BI
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi JavierLopezFALP 

    The variable table is the source of the error, since IF must always return a scalar value.

    In the below expression, the 2nd and 3rd arguments are table rather than scalar values:

    var table = if(countrows(table2) < 1, table1, table2)

     

    One way around this is to rewrite as follows:

    column =
--[state] can adopt values 1 to 5, and there are blank values. 
VAR table1 = FILTER ( RELATEDTABLE ( table2 ), NOT ( ISBLANK ( [state] ) ) )
VAR table2 = FILTER ( RELATEDTABLE ( table2 ), [state] IN { 1, 2 } )
VAR selectTable1 = COUNTROWS ( table2 ) < 1
VAR table =
    UNION ( FILTER ( table1, selectTable1 ), FILTER ( table2, NOT selectTable1 ) )
...

    Does this work as intended?

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi JavierLopezFALP 

    The variable table is the source of the error, since IF must always return a scalar value.

    In the below expression, the 2nd and 3rd arguments are table rather than scalar values:

    var table = if(countrows(table2) < 1, table1, table2)

     

    One way around this is to rewrite as follows:

    column =
--[state] can adopt values 1 to 5, and there are blank values. 
VAR table1 = FILTER ( RELATEDTABLE ( table2 ), NOT ( ISBLANK ( [state] ) ) )
VAR table2 = FILTER ( RELATEDTABLE ( table2 ), [state] IN { 1, 2 } )
VAR selectTable1 = COUNTROWS ( table2 ) < 1
VAR table =
    UNION ( FILTER ( table1, selectTable1 ), FILTER ( table2, NOT selectTable1 ) )
...

    Does this work as intended?

  • JavierLopezFALP's avatar
    JavierLopezFALP
    Frequent Visitor
    1 year ago

    let's review the logic. 

     

    VAR table1 = FILTER ( RELATEDTABLE ( APP2 ), NOT ( ISBLANK ( [state] ) ) )
VAR table2 = FILTER ( RELATEDTABLE ( APP2 ), [state] IN { 1, 2 } )

VAR selectTable1 = COUNTROWS ( table2 ) < 1 || isblank(countrows(table2))

VAR table =
UNION ( FILTER ( table1, selectTable1 ), FILTER ( table2, NOT selectTable1 ) )

     

    I renamed the table2 within the first line's parenthesis as APP2, so as to no confuse with the table2 for the formula

     

    To recapitulate, I created a calculated column in APP1. And this columns returns the answer to the question: does any appointment in APP1 overlap with at least 1 appointment on APP2, in tandem with plenty other conditions?

     

    Also, I added to selectTable the case where the result of the countrows is blank(). Not sure if there is any difference, but better safe than sorry.

     

    If I'm understanding right, var table combines 2 mutulaly excluding tables, thanks to selectTable1.

    • if table2 is empty, table1 could still hold some records, as long as [state] has non blank values
    • but by filtering table1 with selectTable1, in the case table2 HAS records, filtered table1 becomes empty
    • Therefore, the final table is either one that has only 1 and 2 for [state] (table2) or 3 to 5 (filtered table1), even if unfiltered table1 usually hold values 1 to 5

    That should be the logic, ¿right?

     

    • OwenAuger's avatar
      OwenAuger
      Super User
      1 year ago

      Yes that's right 🙂

      The method I suggested is a general "conditional table" pattern:

      -- Pattern for:
-- IF <condition> THEN table1 ELSE table2
UNION (
    FILTER ( table1, <condition> ),
    FILTER ( table2, NOT <condition> )
)

      So the result is equal to table1 only if <condition> is true, otherwise table2.

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi JavierLopezFALP please try this calculated column

     

    HasRelevantAppointment  =
    VAR HasState1Or2 =
        COUNTROWS(
            FILTER(
                RELATEDTABLE(Appointments_ServiceB),
                [State] IN {1, 2}
            )
        ) > 0

    VAR FilteredRows =
        FILTER(
            RELATEDTABLE(Appointments_ServiceB),
            IF(HasState1Or2, [State] IN {1, 2}, NOT(ISBLANK([State])))
        )

    VAR ValidRows =
        FILTER(
            FilteredRows,
            [Date] >= DATE(2024,1,1) && [Date] < DATE(2024,4,1)
        )

    RETURN
        IF(COUNTROWS(ValidRows) > 0, "YES", "NO")