Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Connecting Pivoted to Unpivoted Data Using a Switch Statement

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

example8.JPG

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

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!

 

 

 

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
Solution Sage

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:

PBIDesktop_1vKyM6ovYs.png

Best Regards,

Teige

View solution in original post

3 REPLIES 3
TeigeGao
Solution Sage
Solution Sage

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:

PBIDesktop_1vKyM6ovYs.png

Best Regards,

Teige

Anonymous
Not applicable

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!

Hi @Anonymous ,

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

Best Regards,

Teige

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.