Forum Discussion

Tybaal's avatar
Tybaal
Helper II
5 years ago
Solved

Filter data with an other table condition

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.

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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

     

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      Tybaal
      Helper II
      5 years ago

      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's avatar
        Fowmy
        Super User
        5 years ago

        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