Forum Discussion

tgo's avatar
tgo
Frequent Visitor
8 years ago
Solved

If condition in two tables

Hi everybody,

 

I'm tryin to filter the whole chain of my datas in my network navigation visual. My data look like: 

 

ID from     ID to

Project 1    Product1

Project 1    Product 2

Product 1   Market 1

Product 1   Market 2

Market 1     Target 1

Product 1    Budget 1

Project 2     Product 3

Product 3   ....

 

I want to be abble to filter on Project 1 and see all the chain related to it. My idea was to create a second table like that:

ID                Name

Project 1     Blabla

Project 2     Hi

 

In the first table I'll use the path function to have the whole chain. And I'd like to create a column that says something like "If in the path there is Project1, display "Blabla"). This way I'd put a slicer with "Blabla, Hi" and all the other project.

 

But I'm struggling about creating this last column, as I can't reference the second table in my formula.

 

Does anyone have a solution how to do it, and how my formula should looks like?

 

Thanks in advance guys!!

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi tgo,

     

    What does the second table look like? If you'd like to filter the IDs, I would suggest you do it like below.

    1. Create a new table. 

    New Table =
DISTINCT (
    UNION ( VALUES ( 'table 1'[ID from] ), VALUES ( 'table 1'[ID to] ) )
)

    2. Create a measure.

    Measure =
IF (
    MIN ( 'table 1'[ID from] ) = SELECTEDVALUE ( 'New Table'[ID from] )
        || MIN ( 'table 1'[ID to] ) = SELECTEDVALUE ( 'New Table'[ID from] ),
    1,
    BLANK ()
)

    Finally, you can select one time to filter the two columns.

     

    Best Regards,

    Dale

3 Replies

  • chethan's avatar
    chethan
    Icon for Resolver III rankResolver III
    8 years ago

    tgo

     

    Use below DAX, before that Please create the Relation between both the Tables the Create the Calculated Column

     

    Expected Results =
IF (
    'Sales Data'[Customer Name Index] = RELATED ( 'Customer Data'[Customer Index] ),
    "No Change",
    "Change"
)

     

     

    Regards,

    Chetan K

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi tgo,

     

    What does the second table look like? If you'd like to filter the IDs, I would suggest you do it like below.

    1. Create a new table. 

    New Table =
DISTINCT (
    UNION ( VALUES ( 'table 1'[ID from] ), VALUES ( 'table 1'[ID to] ) )
)

    2. Create a measure.

    Measure =
IF (
    MIN ( 'table 1'[ID from] ) = SELECTEDVALUE ( 'New Table'[ID from] )
        || MIN ( 'table 1'[ID to] ) = SELECTEDVALUE ( 'New Table'[ID from] ),
    1,
    BLANK ()
)

    Finally, you can select one time to filter the two columns.

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi tgo,

     

    Could you please mark the proper answers as solutions please?

     

    Best Regards,

    Dale