DAX - Error on trying to return tables from an IF() statement

[jPinhao]

I'm trying to build a DAX expression that will join a table with 1 of 2 others, depending on whether one of these other tables is empty:

VAR OwnedIDs = IF(ISEMPTY(FilteredOwnedFeatures), 							
                     NATURALINNERJOIN(FeatureOwnedIDs, ALL(Dim_OwnedFeatures)),
                     NATURALINNERJOIN(FeatureOwnedIDs, FilteredOwnedFeatures))

This gives me an error: "The expressions refers to multiple columns. Multiple columns cannot be converted to a scalar value"

I can't figure out what is going on and how to get it to work. There doesn't seem to be any issues with the JOIN statements (they both work in isolation, however they break the if statement. FilteredOwnedFeatures is just a filtered version of Dim_OwnedFeatures so they should both have the same columns, and the resulting joins too.

Why is IF() getting confused with this piece of code? And can I rewritte this in a way that will work? In case this is a more obscure issue, here's the full DAX measure code:

Cumulative Potential Users = 
VAR
  SelectedFeatures = CALCULATETABLE(Fact_FeatureUsed, ALL(Dim_Date))
VAR
  //Filter Dim_OwnedFeatures by only the selected features
  FilteredOwnedFeatures = FILTER(Dim_OwnedFeatures, 
                            CONTAINS(SelectedFeatures, [Feature], Dim_OwnedFeatures[Owned Feature]))
VAR
  // PROBLEM AREA
  // Consider only ownership IDs for features we have selected, or 
  OwnedIDs = IF(ISEMPTY(FilteredOwnedFeatures), 
		NATURALINNERJOIN(FeatureOwnedIDs, ALL(Dim_OwnedFeatures)),
		NATURALINNERJOIN(FeatureOwnedIDs, FilteredOwnedFeatures))

RETURN
 CALCULATE([Cumulative Users],
		ALL(Fact_FeatureUsed[Feature]),
		OwnedIDs)

[v-sihou-msft · Accepted solution]

@jPinhao

In DAX, you can't specify a dynamic filter context in CALCULATE(). And what IF statement returns should be value, if the value_if_ture is a column, it returns the value that corresponds to the current row. You can't make it returns a table context which contains multipe columns as the error message mentioned.

Regards,

[jPinhao]

Thanks for the insight Simon. I found a way around, changing one of my tables to ensure the query wouldn't end up empty and always get a meaningful result back - this has the side effect of having data in there which we don't care about, but it can be filtered out 🙂