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 illustrate...
@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?

techies's avatar
techies
Icon for Super User rankSuper 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")