Filter Context not propagating with IF function used in a measure

[Anonymous]

Hi All,

Having an issue with a basic IF logic used in a measure. My IF statement is as follows:

Volume Check = 
IF(
    [Volume] > 10000,
    "Flag",
    "Ok"
)

The measure works and my table visual does what I want:

Project Name	Launch Date	Project Number	Volume Check
A	10/15/2020	12345	Ok
B	1/5/2025	24690	Ok
C	4/7/2024	49380	Flag
D	4/1/2024	98760	Ok
E	4/10/2023	19752	Ok
F	4/8/2024	39504	Ok
G	4/3/2023	79008	Flag

My issue occurs when I drag a field into the table visual from one of my other lookup tables. Example: if I drag in the "Business Name" field from the Business table it repeats everything like you would see if a relationship did not exist between lookup and source tables. The table looks as follows:

Business	Project Name	Launch Date	Project Number	Volume Check
Business 1	A	10/15/2020	12345	Ok
Business 2	A	10/15/2020	12345	Ok
Business 3	A	10/15/2020	12345	Ok
Business 1	B	1/5/2025	24690	Ok
Business 2	B	1/5/2025	24690	Ok
Business 3	B	1/5/2025	24690	Ok
Business 1	C	4/7/2024	49380	Flag
Business 2	C	4/7/2024	49380	Flag
Business 3	C	4/7/2024	49380	Flag
Business 1	D	4/1/2024	98760	Ok
Business 2	D	4/1/2024	98760	Ok
Business 3	D	4/1/2024	98760	Ok
Business 1	E	4/10/2023	19752	Ok
Business 2	E	4/10/2023	19752	Ok
Business 3	E	4/10/2023	19752	Ok
Business 1	F	4/8/2024	39504	Ok
Business 2	F	4/8/2024	39504	Ok
Business 3	F	4/8/2024	39504	Ok
Business 1	G	4/3/2023	79008	Flag
Business 2	G	4/3/2023	79008	Flag
Business 3	G	4/3/2023	79008	Flag

The issue is not my data model. I believe I need to force the filter context with my IF measure. Does anyone know what I am doing wrong here and how I can fix this issue? Thank you!

[tamerj1]

@Anonymous 

When adding columns from different tables the engine creates a cross join table generating all possible unique combinations. Allways add columns as measures perhaps using SELECTEDVALUE 

[Anonymous]

Hi @tamerj1 

Thank you for the response! What would the DAX look like for this combined with my measure above?

[tamerj1]

It depends on your data model. Can you please share a screenshot of you data model and the expected results table indicating the the table to which each column belongs. 

[Anonymous]

Hi @tamerj1 

Below is a screenshot of the data model and the desired result. The measure I am trying to create is currently stored in a Measure Table to keep things organized in the fields pane (screenshot provided) but the expression [Volume] in my formula above is calculated from the "Activity Metrics Detail" table.

Data Model

Desired Result

Measure Table

Thank you!

[tamerj1]

Hi @Anonymous 

Yes you are 100% correct. But you also have to account for the changes in the filter context every time you add a column to the visual. Here are some screen shots of a similar data model

[Anonymous]

Hi @tamerj1 

Great explanation and I appreciate the diagram. We're on the same page that fact tables cannot filter dimensions and, dimensions can filter fact tables. There's no confusion around that. The way my measure is built is the issue because when I add Project ID, Business ID, and Date ID in a table and then add a slicer to slice by, let's say, Business Name, it filters perfectly. The issue occurs when I add fields from different dimensions in addition to my Volume Check measure.

This is how I always structure my data models and my measures work fine. I have never used an IF logic before which is why I think it's missing something.

How do I fix the measure to work correctly?

[tamerj1]

@Anonymous 

I don't think IF has anything to do with that. Place the measure [Volume] in the table and see what values you get. 
again by placing a filter from another dim table you have changed the filter context. The values of the comparison measure will be diffrent (less) 

[Anonymous]

Hi @tamerj1 

Everything works as expected. I created a table to test by placing Project Number, Business Unit, and Launch Year (all fields from my dimensions) along with my Volume measure and filter context works as it should.

For reference, below is the calculation for Volume. One thing to note is that I like to branch my measures, so the "MASTER TOTAL" measure is simply a SUM of the values column in my fact table.

Volume =

CALCULATE(

      [MASTER TOTAL],

      'Activity Metrics Detail'[Line Item] = "Volume"

)

[tamerj1 · Accepted solution]

@Anonymous 

You can modify your measure

Volume Check =
IF ( [Volume] > 10000, "Flag", IF ( [Volume] > 0, "Ok" ) )

or

Volume Check =
IF ( NOT ISBLANK ( [Volume] ), IF ( [Volume] > 10000, "Flag", "Ok" ) )

[tamerj1]

@Anonymous 

Business table has no relationship with projects. Adding the business number column has no meaning based your data model. If you remove it then the lookup 'Projects' table can safely filter the fact table otherwise a meaningless crossjoin table will be creared. Do you have any coulmns that can porovide a direct connections between the two tables?

[Anonymous]

Hi @tamerj1 

I am not sure I understand what you mean. Each business is involved with many projects. The project, and therefore the business, will create facts based on events that take place over time. The reason I have created lookup tables for projects and business is to optimize the data model using the start schema method.

Project Number is found in both the Project table (dimension table) table and the Activity Metrics Detail table (fact table)

Finance MBU is found in both the Business table (dimension table) table and the Activity Metrics Detail table (fact table)

Can you clarify what you mean?