Variable seems to break model?

[vckbx]

Ok I have the following simple model:

I've created the following measure on Registration table:

Active = 
var _date = FIRSTDATE('Date'[Date])
var _start = SELECTEDVALUE('Registration'[StartDate])
var _end = SELECTEDVALUE('Registration'[EndDate])
return
IF(
    _start <= _date
    && OR(_end >= _date,_end=BLANK())
    ,1,0
    )

My measure works as expected:

But breaks when I pull a field from "Teams" into the Table:

Thank you for your help!

[bcdobbs · Accepted solution]

Hi @vckbx ,

Changing your measure to the following will solve the problem:

Active = 
VAR _date = FIRSTDATE('Date'[Date])
VAR _start = SELECTEDVALUE('Registration'[StartDate])
VAR _end = SELECTEDVALUE('Registration'[EndDate])
RETURN
SWITCH ( TRUE (),
    ISEMPTY ( Registration ), BLANK (),
    _start <= _date && ( _end >= _date || _end=BLANK() ), 1,
    0
    )

I'll send a follow up trying to explain why you're hitting this issue in the first place.

[bcdobbs]

So the first thing is to recreate the issue with the simplest DAX possible. If you replace your "Active" measure with 

Active = 1

You will find that you get exactly the same rows in the visual. In effect the your measure is returning every combination (a cross join/cartesian product) between Team and Registration. The alteration I suggested to your measure checks to see if for a particular team the Registration table is empty, and return blank if it is which stops this cross join from happening.

All of this is being caused by the query Power BI writes under the surface against it's engine. Running the performance analyser and neatening up the query shows that the table visual runs something like:

DEFINE
  VAR FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Date'[Date])),
      'Date'[Date] >= DATE(2022, 4, 2)
    )

  VAR Result = 
    SUMMARIZECOLUMNS(
      'Registration'[Person],
      'Registration'[Start Date],
      'Registration'[End Date],
      'Team'[Mascot],
      'Team'[Team],
      FilterTable,
      "Active", [Active]
    )

EVALUATE
  Result

Refering to DAX Guide there is the following note regarding SUMMARIZECOLUMNS:

"Filters in SUMMARIZECOLUMNS only apply to group-by columns from the same table and to measures. They do not apply to group-by columns from other tables directly, but indirectly through the implied non-empty filter from measures. In order to apply a filter to the group-by column unconditionally, apply the filter through a CALCULATETABLE function that evaluates SUMMARIZECOLUMNS."

The date filter doesn't get applied directly to any column so it is literally forming a caresian product of Registrations and Teams for which your original "Active" measure will always return a value. The only way to correct this is to force the measure to return blank on rows we don't want.

Another way to see what SUMMARIZECOLUMNS is doing is to create a calculated table in PowerBI with the following DAX:

Test Table = 
 SUMMARIZECOLUMNS(
      'Registration'[Person],
      'Registration'[Start Date],
      'Registration'[End Date],
      'Team'[Mascot],
      'Team'[Team]
    )

I wrote up a similar solution in the following blog: SUMMARIZECOLUMNS and Filters – Dobbs On Data

To quote Alberto "SUMMARIZECOLUMNS is likely to be the king of DAX shenanigans"

[vckbx]

Awesome, thank you so much for the detailed response!