cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tybaal
Helper I
Helper I

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.

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
Fowmy
Super User IV
Super User IV

@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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

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])

 

 

@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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Thank you for your help.

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

I made this schema :

Tybaal_0-1599484693588.png

Have you another idea?

MFelix
Super User III
Super User III

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

MFelix
Super User III
Super User III

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?

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User III
Super User III

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.