LookupValue with variable to set column reference

I have a query pulling a bunch of thermocouple data from some oven equipment per part we will call this table CAC_AllData. A second query that is pulling the what thermocouples were used for that part table is call CAC_Attach.

The issue i am having is that the CAC_Alldata table creates columns for all 260 thermocouples, but the part only uses 10, and the 10 used could be different every time. So because of this I created the SNAttach query to determine which TCs were used on the part.

What i am trying to do is create a new table with DataID number Serial number and the 10 thermocouples used.

I can determine which TC was used by using a lookup formula

TC2 =

LOOKUPVALUE(CAC_ATTACH[AttachmentName],CAC_ATTACH[SerialNumber],[SerialNumber],CAC_ATTACH[Rank Attachment],3)

This will give me any range between TC1 - TC260

What I am trying to do is set that lookupvalue formula so the the reult table is set up as Variable using a formula.

TC2 =

VAR TC =

"CAC_AllData["&LOOKUPVALUE(CAC_ATTACH[AttachmentName],CAC_ATTACH[SerialNumber],[SerialNumber],CAC_ATTACH[Rank Attachment],3)&"]" ------ This would create the Table and Column Name

Result

LOOKUPVALUE(TC, CAC_AllData[SerialNumber],[SerialNumber], CAC_AllData[DataPointID],[Datapointid])

so my final table would look something like this

Is this even possible. Or am i going about it a wrong way.

Need Help

v-kathullac
1 year ago
Hi ,

can you try with below debug steps to solve your issue.

Load your raw data table CAC_AllData into Power BI In Power Query, select all columns from PTC1 to PTC260.

Right-click and choose Unpivot Columns Rename the resulting columns:

Attribute → AttachmentName

Value → PTC_Value

You will now have a table with columns: SerialNumber, AttachmentName, PTC_Value

Load or create a separate table called UsedAttachments with columns: SerialNumber, AttachmentName, Rank

In Power BI model view, create relationships:

Between CAC_Unpivoted[SerialNumber] and UsedAttachments[SerialNumber]

Between CAC_Unpivoted[AttachmentName] and UsedAttachments[AttachmentName]

Optionally, instead of 2 relationships, create a calculated column in both tables:

Serial_Attach_Key = SerialNumber & "-" & AttachmentName

Create a relationship on Serial_Attach_Key between the two tables

Create a calculated column in the unpivoted table:

IsUsed =
CALCULATE(
COUNTROWS(UsedAttachments),
FILTER(
UsedAttachments,
UsedAttachments[SerialNumber] = CAC_Unpivoted[SerialNumber] &&
UsedAttachments[AttachmentName] = CAC_Unpivoted[AttachmentName]
)
) > 0
Use this column to filter your visuals to show only rows where IsUsed = TRUE

Alternatively, create a calculated table with only matching rows:

FilteredPTCData =
FILTER (
CAC_Unpivoted,
CALCULATE (
COUNTROWS (UsedAttachments),
UsedAttachments[SerialNumber] = CAC_Unpivoted[SerialNumber],
UsedAttachments[AttachmentName] = CAC_Unpivoted[AttachmentName]
) > 0
)

If it is solved, please mark the helpful reply or share your solution and Accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya.

5 Replies

Sahir_Maharaj

Super User

1 year ago

Hello TommySidds,

If you're building a summary table, you can pivot it back using this DAX. Could you please try this approach:

ResultTable =
VAR Base =
    SELECTCOLUMNS (
        FILTER (
            Unpivoted_CAC_AllData,
            Unpivoted_CAC_AllData[ThermocoupleName] IN 
                SELECTCOLUMNS (
                    FILTER (
                        CAC_ATTACH,
                        CAC_ATTACH[SerialNumber] = Unpivoted_CAC_AllData[SerialNumber]
                    ),
                    "TC", CAC_ATTACH[AttachmentName]
                )
        ),
        "DataPointID", [DataPointID],
        "SerialNumber", [SerialNumber],
        "Thermocouple", [ThermocoupleName],
        "Value", [Value]
    )
RETURN
    SUMMARIZECOLUMNS (
        Base[DataPointID],
        Base[SerialNumber],
        "TC1", CALCULATE(MAX(Base[Value]), Base[Thermocouple] = "TC1"),
        "TC2", CALCULATE(MAX(Base[Value]), Base[Thermocouple] = "TC2"),
        ...
        "TC10", CALCULATE(MAX(Base[Value]), Base[Thermocouple] = "TC10")
    )

TommySidds

Frequent Visitor

1 year ago

I couldnt get it to work, I am also still learning PowerBI and DAX.

Here is a portion of the raw data CAC_AllData (Note this table will have over 260 PTCXXX items and i can use any 10 PTCs at any time)

SerialNumber	PTC1	PTC10	PTC100	PTC101	PTC102	PTC103	PTC104	PTC105	PTC106	PTC107	PTC108	PTC109	PTC11	PTC110	PTC111	PTC112
SN0001	0	0	0	0	72.30000305	72.8	72	71.900002	72.400002	0	0	0	0	0	0	73.5
SN0001	0	0	0	0	72.30000305	72.8	72	71.900002	72.400002	0	0	0	0	0	0	73.5
SN0001	0	0	0	0	72.40000153	72.9	72.1	71.900002	72.400002	0	0	0	0	0	0	73.5
SN0001	0	0	0	0	72.40000153	72.8	72.1	71.900002	72.5	0	0	0	0	0	0	73.5
SN0001	0	0	0	0	72.30000305	72.9	72.1	72	72.5	0	0	0	0	0	0	73.599998
SN0001	0	0	0	0	72.40000153	72.8	72.1	72	72.5	0	0	0	0	0	0	73.699997
SN0001	0	0	0	0	72.5	72.8	72.1	72.099998	72.599998	0	0	0	0	0	0	73.800003
SN0001	0	0	0	0	72.59999847	73	72.3	72.199997	72.800003	0	0	0	0	0	0	73.900002
SN0001	0	0	0	0	72.80000305	73.2	72.5	72.300003	72.900002	0	0	0	0	0	0	74.199997
SN0001	0	0	0	0	73.09999847	73.4	72.7	72.5	73.199997	0	0	0	0	0	0	74.400002
SN0001	0	0	0	0	73.30000305	73.6	73	72.699997	73.400002	0	0	0	0	0	0	74.800003
SN0001	0	0	0	0	73.59999847	73.8	73.2	73	73.699997	0	0	0	0	0	0	75.099998
SN0001	0	0	0	0	73.90000153	73.9	73.5	73.199997	73.900002	0	0	0	0	0	0	75.400002
SN0001	0	0	0	0	74.19999695	74.2	73.8	73.5	74.300003	0	0	0	0	0	0	75.800003
SN0001	0	0	0	0	74.59999847	74.6	74.3	73.900002	74.800003	0	0	0	0	0	0	76.400002
SN0001	0	0	0	0	75.19999695	75.1	74.9	74.400002	75.400002	0	0	0	0	0	0	77
SN0001	0	0	0	0	75.90000153	75.6	75.4	74.900002	75.900002	0	0	0	0	0	0	77.900002
SN0001	0	0	0	0	76.59999847	76.4	76.2	75.599998	76.699997	0	0	0	0	0	0	78.699997

Here is a query that tell me what attachments were used for that spefic serial number. I also created a rank formula to rank the TCs used. That way i could use that to look up TC 1, TC 2.....

SerialNumber	AttachmentName	Rank
SN0001	PTC102	1
SN0001	PTC103	2
SN0001	PTC104	3
SN0001	PTC105	4
SN0001	PTC106	5
SN0001	PTC112	6
SN0001	PTC113	7
SN0001	PTC114	8
SN0001	PTC116	9
SN0001	PTC118	10

I want to be able to create a table that only pulls the data from the TCs that were used. In excel i can do a lookup that will use the TC name in the attach table and set that as my lookup column. I just cant figure out how to do that with DAX/PowerBI. Maybe there is even a better way to do this.

		PTC102	PTC103	PTC104	PTC105	PTC106
DataPointID	Serial Number	TC1	TC2	TC3	TC4	TC5
334638	SN0001	72.3	72.8	72.0	71.9	72.4
334639	SN0001	72.3	72.8	72.0	71.9	72.4
334640	SN0001	72.4	72.9	72.1	71.9	72.4
334641	SN0001	72.4	72.8	72.1	71.9	72.5
334642	SN0001	72.3	72.9	72.1	72.0	72.5
334643	SN0001	72.4	72.8	72.1	72.0	72.5
334644	SN0001	72.5	72.8	72.1	72.1	72.6
334645	SN0001	72.6	73.0	72.3	72.2	72.8
334646	SN0001	72.8	73.2	72.5	72.3	72.9
334647	SN0001	73.1	73.4	72.7	72.5	73.2
334648	SN0001	73.3	73.6	73.0	72.7	73.4
334649	SN0001	73.6	73.8	73.2	73.0	73.7
334650	SN0001	73.9	73.9	73.5	73.2	73.9
334651	SN0001	74.2	74.2	73.8	73.5	74.3
334652	SN0001	74.6	74.6	74.3	73.9	74.8
334653	SN0001	75.2	75.1	74.9	74.4	75.4

hopefully this helps.

v-kathullac
Community Support
1 year ago
Hi ,

can you try with below debug steps to solve your issue.

Load your raw data table CAC_AllData into Power BI In Power Query, select all columns from PTC1 to PTC260.

Right-click and choose Unpivot Columns Rename the resulting columns:

Attribute → AttachmentName

Value → PTC_Value

You will now have a table with columns: SerialNumber, AttachmentName, PTC_Value

Load or create a separate table called UsedAttachments with columns: SerialNumber, AttachmentName, Rank

In Power BI model view, create relationships:

Between CAC_Unpivoted[SerialNumber] and UsedAttachments[SerialNumber]

Between CAC_Unpivoted[AttachmentName] and UsedAttachments[AttachmentName]

Optionally, instead of 2 relationships, create a calculated column in both tables:

Serial_Attach_Key = SerialNumber & "-" & AttachmentName

Create a relationship on Serial_Attach_Key between the two tables

Create a calculated column in the unpivoted table:

IsUsed =
CALCULATE(
COUNTROWS(UsedAttachments),
FILTER(
UsedAttachments,
UsedAttachments[SerialNumber] = CAC_Unpivoted[SerialNumber] &&
UsedAttachments[AttachmentName] = CAC_Unpivoted[AttachmentName]
)
) > 0
Use this column to filter your visuals to show only rows where IsUsed = TRUE

Alternatively, create a calculated table with only matching rows:

FilteredPTCData =
FILTER (
CAC_Unpivoted,
CALCULATE (
COUNTROWS (UsedAttachments),
UsedAttachments[SerialNumber] = CAC_Unpivoted[SerialNumber],
UsedAttachments[AttachmentName] = CAC_Unpivoted[AttachmentName]
) > 0
)

If it is solved, please mark the helpful reply or share your solution and Accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya.
v-kathullac
Community Support
1 year ago
Hi @TommySidds ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.
v-kathullac
Community Support
1 year ago
Hi @TommySidds ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

Forum Discussion

LookupValue with variable to set column reference

5 Replies

Related content

UDF with variable table reference and fixed column reference

Dataflow Gen2: Variable Library references fail in Connection Settings

lookupvalue

Setting a variable in LOOKUPVALUE

lookupvalue PBI