Forum Discussion

vckbx's avatar
vckbx
Helper I
4 years ago
Solved

Variable seems to break model?

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!

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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.

3 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    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's avatar
      bcdobbs
      Community Champion
      4 years ago

      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's avatar
        vckbx
        Helper I
        4 years ago

        Awesome, thank you so much for the detailed response!