Filter data with an other table condition

[Tybaal]

Hello, 

I'd like to make something seems simple but I do not reach my goal...

I have to display data in a column with a condition on a column from an other table.

Example : 

if ('Dimension 1'[field 1] = 'X'; 'Dimension 2'[field 1])

else if (('Dimension 1'[field 1] = 'Y'; 'Dimension 2'[field 2])

Someone to help me?

(sorry I know that it seems simpke but I'm a beginner)

Thank you in advance.

[Fowmy]

@Tybaal 

You need to provide more detail as to how your model is setup. If the value is X in Dimension 1 to go and get value from Dimension2, either there should be a relationship that you can use to get the value using the RELATED function or a condition to search value using a function like LOOKUPVALUE?

Better share some dummy data showing both the tables and the expected output.

[Tybaal]

Thank you for your answers.

I will detail more 🙂

In fact I have 2 dimensions, both are linked to a fact table in my model :

I want to create a new column in my fact table with this logic : 

New fact table column = If ('Dim contract type'[ContractType code] = 'P'; 'Dim Terms'[P Term])

                                         Else if ('Dim contract type'[ContractType code] = 'S'; 'Dim Terms'[S Term])

[Fowmy]

@Tybaal 

Can you try this code to create a new column in the Fact Table:

New fact table column = 
If (
    RELATED('Dim contract type'[ContractType code]) = 'P', 
    RELATED('Dim Terms'[P Term]),
    
    if (
        RELATED('Dim contract type'[ContractType code]) = 'S',
        RELATED('Dim Terms'[S Term]))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Tybaal]

Thank you for your help.

Unfortunately, I tried your proposition and it still doesn't work.

I made this schema :

Have you another idea?

[MFelix · Accepted solution]

Hi @Tybaal ,

Try the following formula:

Column =
IF (
    RELATED ( ContractType[ContractType] ) = "X";
    RELATED ( 'Condition'[ConditionX] );
    IF (
        RELATED ( ContractType[ContractType] ) = "Y";
        RELATED ( 'Condition'[ConditionY] )
    )
)

If you only have two conditions then the formula can be.

Column =
IF (
    RELATED ( ContractType[ContractType] ) = "X";
    RELATED ( 'Condition'[ConditionX] );
    RELATED ( 'Condition'[ConditionY] )
)

Check PBIX file with both versions

[MFelix]

Hi @Tybaal ,

I'm assuming that the tables are related by a contract number? what is the link between the fact and the dimensions tables?

Looking at your request I assume that you would need to use a lookup to get the data but the lookup needs to have the common link (is similar to VLOOKUP in excel).

Can you share a small data sample?

[MFelix]

Hi @Tybaal ,

This type of filtering is possible however there is the need of more information. Can you please share a sample of your data and expected result? I'm assuming that there is some common field between both tables that allow for you to get the filtering.