Connecting Pivoted to Unpivoted Data Using a Switch Statement

[Anonymous]

Hi data friends!

I need some help to get my DAX to work.
I have 2 identical data sets of dates. I loaded them both into Power BI. I unpivoted one of them and left the other as is (seen below).

Table 1: Regular

Table 2: Unpivoted (Milestone B-D) I am trying to create the Start Date


I created a relationship between these two tables using the ID column.

The start date is the column/measure I'm trying to create with DAX. This is what I tried.

Start Date = 
SWITCH (
    TRUE (),
    'TABLE2'[Attribute] = "Milestone B", 'TABLE1'[Milestone A],
    'TABLE2'[Attribute] = "Milestone C", 'TABLE1'[Milestone B],
    'TABLE2'[Attribute] = "Milestone D", 'TABLE1'[Milestone B],
    "else"

    )

I receive the error:

A single value for column "Milestone A" in the TABLE1 cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum, to get a single result. 

I think perhaps I need to add something to the switch statement, that will single out the specific ID for each milestone in Table 1.

Hope this makes sense, any help appreciated!

[TeigeGao · Accepted solution]

Hi @Anonymous ,

In your scenario, we will need to use the LOOKUPVALUE() function to get the data from Table1, we can create a calculated column using the following DAX query:

Start Date =
IF (
    Unpivot[Attribute] = "Milestone B",
    LOOKUPVALUE ( 'Regular'[Milestone A], 'Regular'[ID], Unpivot[ID] ),
    IF (
        Unpivot[Attribute] = "Milestone C",
        LOOKUPVALUE ( 'Regular'[Milestone B], 'Regular'[ID], Unpivot[ID] ),
        IF (
            Unpivot[Attribute] = "Milestone D",
            LOOKUPVALUE ( 'Regular'[Milestone B], 'Regular'[ID], Unpivot[ID] )
        )
    )
)

The result will like below:

Best Regards,

Teige

[Anonymous]

Thank you @TeigeGao !

When you use the terms 'Unpivot' and 'Regular' I assume those are TABLE2 and TABLE1, respectively?
If so, it doesn't appear to let me reference TABLE1 while creating a column in TABLE2 (and vice versa).

I looked at this answer regarding access columns from different tables.... could you perhaps update your DAX to include this?

Thank you!

[TeigeGao]

Hi @Anonymous ,

We can create relationship between Table 1 and Table 2 on the column ID

Best Regards,

Teige