CODEWITHSUNDEEP
excelpowerbidaxpowerbi-desktoppowerpivot
David
David

Reputation: 23

Certain Dax Measure Ignoring Filters

I am very new to Power BI, Power Pivot & DAX and I feel like I am missing a fundamental concept of how filter context and row context is working so some help is appreciated.

Here is what I have set up:

I have my fact table with the following, Order Number, Customer ID, Contact ID, cPortal Code (Data Type = Whole Number), pPortal Code (Data Type = Whole Number) & Order Date (Data Type Date). You can have multiple Contact IDs per Order so Order Number may be duplicated. I have a separate Calendar Table with the appropriate relationship back to the fact table. (note: cPortal Code & pPortal Code are calculated based on Customer ID & Contact ID information pulled from other dimensional tables, not sure if this is relevant)

Measure 1: Portal Code := SUMX('Fact Table','Fact Table'[cPortal Code] + 'Fact Table'[pPortal Code])

Measure 2: Portal Interaction := IF([Portal Code] = 1, "Hot", "Cold")

I want to look at orders that were created on a certain date (or dates) and see if their Portal Interaction is "Hot" or "Cold"

If I create a pivot table (in excel) with this data model I have the following happen:

Scenario 1

Filter Calendar Date to 10/2/2023

Rows: Order Number

Values: Portal Code

Results are that I see the three orders that were created on 10/2/2023 and their appropriate Portal Code value

Filter Date = 10/2/2023

|Order NO  | Portal Code |
| -------- | ------------|
| C1001    | 1           |
| C1002    | 11          |
| C1003    | 1           |

Scenario 2

Filter Calendar Date to 10/2/2023

Rows: Order Number

Values: Portal Code & Portal Interaction

Results here are that my date filter gets ignored by the Portal Interaction measure but not the Portal Code measure. The resulting table is a list of all Order Numbers regardless of the date created with a blank cell for Portal Code (except on the appropriate created dates) and the appropriate text for Portal Interaction for every single Order Number.

Filter Date = 10/2/2023

|Order NO  | Portal Code | Portal Inter. |
| -------- | ------------|---------------|
| C999     |             |  HOT          |
| C998     |             |  HOT          |
| C456     |             |  Cold         |
| C123     |             |  Hot          |
| C1001    | 1           |  Hot          |
| C1002    | 11          |  Cold         |
| C1003    | 1           |  Hot          |

What is the basic concept that I'm missing?

Thanks!

Data Model Screenshot

Here is a Google Drive link to a similar model. I have 2 pivot tables set up. The one on the left is just showing me the total quantities for the Orders/Transactions that took place on the last day. The Pivot Table on the right has the same columns with the addition of "Quantity Indicator" measure. When this measure is added the date filter is ignored. Link to a similar Model

Upvotes: 2

Views: 563

Answers (1)

davidebacci
davidebacci

Reputation: 30289

You can fix it with this:

Quantity Indicator:=
IF(NOT(ISBLANK([Total Quantity])),
    SWITCH(TRUE(),
    [Total Quantity]<5,"Cold",
    AND([Total Quantity]>=5,[Total Quantity]<10),"Medium",
    [Total Quantity]>=10,"HOT HOT HOT"
    )
)

In a DAX comparison, BLANK is considered as zero. Therefore, a measure that evaluates to blank is treated as zero and any non-existing combination of the dimensions satisfies the IF statement.

From https://www.sqlbi.com/articles/optimizing-conditions-involving-blank-values-in-dax/

Upvotes: 2

Related Questions