Forum Discussion
DAX Calculated Column with "variable tables" as variables.
The variable
tableis the source of the error, sinceIFmust 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?
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?
Super UserYes 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.