CODEWITHSUNDEEP
powerbidax
CodeMonkey
CodeMonkey

Reputation: 3668

PowerBI DAX: Creating Measure which accepts partial visual filters from same table

I have a Fact table and a Calendar table. The calendar table has a column boolean column denoting if a day is a holiday.

Relationship

I wan't to create a measure: AllSales which ignores any filters on Holiday but respects filters on all other Calendar columns. See later for applied visual level filters.

The measure below works except for total sums:

AllSales = CALCULATE(SUM(FactSales[Sales]); ALL('Calendar'[Holiday]); ALL('Calendar'[DateKey]))

See below image for the result. Individual weeks are OK as those are ok, but notice the years that sum to the wrong total. The Bar chat displays correctly but the totals in the matrix table are wrong. The total of AllSales should be 700 due to the visual level filter Calendar[DateKey] that removes the week one of 2019, yet it is still counted in the total.

Wrong Result

So to sum up:

If i remove the ALL(Calendar[DateKey]) its like my ALL(Calendar[Holiday]) filter is just ignored and AllSales becomes equivalent to Sales which is the raw data column.

If you wan't to play with the data here are the two M queries to create Calendar and FactSales tables. Just link them by DateKey 1:* one directional.

Create two new queries and paste below code into each in the Advanced Editor(M language editor)

Calendar query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MAQiJR0wB0iBmCFFpalKsTpICoxQFbgl5hSjqTAmqMIEocIIuwpT3CqMDJAcamSA6VCoAiNUBViMMCaowgShAoczTLGqiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, year = _t, Week = _t, Holiday = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"year", Int64.Type}, {"Week", Int64.Type}, {"Holiday", type logical}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"year", "Year"}})
in
    #"Renamed Columns"

FactSales query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjIwtNQ1MAQipVgdDEEjbILG2ARNsAmaoggaGWCxCCpohE3QGJugCTZBoEWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sales = _t, DateKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"DateKey", type date}})
in
    #"Changed Type"

I've come to the conclusion that DAX/TabularModel do not really work well with multiple slicers/filters from the same table and that i should split them up. I would still like to know if someone has a DAX solution to this problem.

Upvotes: 3

Views: 1692

Answers (1)

greggyb
greggyb

Reputation: 3798

So, what happens when you build a visual is that a DAX query is created to return a resultset that is then plotted. You can see an excerpt of the query below. There is more after this, but we have everything we need to understand the behavior you're seeing.


DEFINE
  // This is a table-valued variable, which holds all dates which will be in context.
  // This is based on your visual filter.
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendar'[DateKey])),
      'Calendar'[DateKey] >= (DATE(2019, 1, 3) + TIME(0, 0, 1))
    )

  // This is a table-valued variable which holds the 1-row, 1-column table based on
  // 'Calendar'[Holiday] holding the value FALSE. This is from your slicer
  VAR __DS0FilterTable2 = 
    TREATAS({FALSE,
      BLANK()}, 'Calendar'[Holiday])

  // This builds up your resultset
  VAR __DS0Core = 
    // SUMMARIZECOLUMNS lists your grouping columns, applies filters, and projects
    // measures onto your resultset
    SUMMARIZECOLUMNS(
      // This rollup defines the grouping columns, whose values are shown on your visual.
      // The rollup defines which columns get subtotals, which means we calculate an
      // additional row in the resultset with filter context cleared from the column
      // which is rolling up. In this case, we'll have a row with no Year context,
      // but with Week context, a row with no Week context, but with Year context, and
      // a row with no context on either.
      ROLLUPADDISSUBTOTAL('Calendar'[Year], "IsGrandTotalRowTotal", 'Calendar'[Week], "IsDM1Total"),

      // I'm separating out the filter args here. These are evaluated in a logical
      // AND. The first includes all dates that pass your filter on the visual.
      // The second includes only 'Calendar'[Holiday]=FALSE. The logical AND of these
      // includes:
      //   {2019-01-04, 2019-01-05, 2020-01-02, 2020-01-03, 2020-01-04, 2020-01-05}
      // Note that we're missing 2020-01-01, which passes the date test, but is a holiday.
      // This is the context for evaluating our measures.
      __DS0FilterTable,
      __DS0FilterTable2,
      // end of filter args

      "Sales_Amount", 'FactSales'[Sales Amount],
      "All_Sales", 'FactSales'[All Sales],
    )

So based on how that is being evaluated, by the time it gets around to your measure, the filter context for dates is:

<whatever is on your visual at that level>

INTERSECT

{2019-01-04, 2019-01-05, 2020-01-02, 2020-01-03, 2020-01-04, 2020-01-05}

That's the filter context in which we evaluate:

AllSales =
CALCULATE (
  SUM ( FactSales[Sales] );
  ALL ( 'Calendar'[Holiday] );
  ALL ( 'Calendar'[DateKey] )
)

So without your ALL ( 'Calendar'[DateKey] ) the filter context for your sum does not include 2020-01-01. Unfortunately, your filter for 'Calendar'[DateKey] > 2019-01-03 also gets cleared by this ALL, which explains your grand total issue. The grand total is no Year context (based on the rollup in SUMMARIZECOLUMNS), no Week context (based on the same), and then based on your measure no Holiday context and no DateKey context.

The reason it works for your column chart is that there is no grand total to show.

Now, a solution that will likely be unsatisfying, because it will seem so very similar, is to define a new column, YearWeek and filter on that, instead of filtering on 'Calendar'[DateKey].

So:

//column
'Calendar'[YearWeek] = 'Calendar'[Year] * 100 + 'Calendar'[Week]

//measure:
All Sales =
CALCULATE (
  SUM ( 'FactSales'[Sales] );
  ALL ( 'Calendar'[Holiday] )
)

Then you can define a filter on 'Calendar'[YearWeek] > 201901. This is logically equivalent, but not DAX equivalent. Your query looks like this now:

DEFINE
  // This is now on 'Calendar'[YearWeek], instead of 'Calendar'[DateKey]
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendar'[YearWeek])),
      'Calendar'[YearWeek] > 201901
    )

  // same as before
  VAR __DS0FilterTable2 = 
    TREATAS({FALSE,
      BLANK()}, 'Calendar'[Holiday])

  // Textually same as before, but different context
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Calendar'[Year], "IsGrandTotalRowTotal", 'Calendar'[Week], "IsDM1Total"),

      // Here are the filters again:
      // Our intersection of 'Calendar'[Holiday]=FALSE and 'Calendar'[YearWeek]>201901.
      // The 'Calendar'[YearWeek] filter is: {201902, 202001, 202002}
      // so now our filter context doesn't explicitly exclude the date 2020-01-01, which
      // was the problem above.
      __DS0FilterTable,
      __DS0FilterTable2,
      // end of filters

      "Sales_Amount", 'FactSales'[Sales Amount],
      "All_Sales", 'FactSales'[All Sales]
    )

...

Based on the filter context above, the measure works just fine.

So, what's the difference between the following two scenarios:

  1. Filter on 'Calendar'[Holiday] and 'Calendar'[DateKey]
  2. Filter on 'Calendar'[Holiday] and 'Calendar'[YearWeek]

They're both sets of filters on the same table. The difference is that 'Calendar'[Holiday] and 'Calendar'[DateKey] are at the same grain. The intersection of them explicitly excludes a date you want. And so you needed an extra ALL to override that. ALL is too broad, though, which messed with your grand totals.

'Calendar'[Holiday] and 'Calendar'[YearWeek] are at different grains. If you had a full week of days in a single YearWeek that were all 'Calendar'[Holiday]=TRUE, then you'd run into the same problem as above, because your filter on 'Calendar'[Holiday] would inadvertently remove an entire 'Calendar'[YearWeek] that you want to include.

The critical point in all of this is that DAX filter contexts are just lists of literal values in columns. The emphasis is important. I was scratching my head a lot in answering this question, but breaking it down to the literal tables of values got me back on track.

This was a really good question, and thanks for asking it. It made me drill into some nuance of DAX I haven't had to think much about lately. And your intuition is spot on on how things should behave.

Upvotes: 5

Related Questions