Forum Discussion

Frequent Visitor

1 year ago

Solved

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

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.

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.

Forum Discussion

LookupValue with variable to set column reference

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