I cannot solve the problem Power BI Dax how to find value for Data3 column

[MichalMIK]

Hi, I have a problem. I cannot find proper command. I have a table for example there will be 3 column. Data1, Data2, Data3

In Data1 values are unique, in Data2 there are values from Data1 but not in all. I would like to find the value in column Date3 from column Date1 where the value in column Date2 occurs. I tried LOOKUPVALUE but somehow it does not work properly.

any suggestion?

[123abc]

In Power BI DAX, you can use the RELATED function along with FILTER to achieve this. Assuming your table is named "YourTable" and the columns are named "Data1," "Data2," and "Data3," you can create a calculated column for "Data3" using the following DAX formula:

Data3 =
CALCULATE(
MAX(YourTable[Data1]),
FILTER(
YourTable,
YourTable[Data2] = EARLIER(YourTable[Data2])
)
)

In Power BI DAX, you can use the RELATED function along with FILTER to achieve this. Assuming your table is named "YourTable" and the columns are named "Data1," "Data2," and "Data3," you can create a calculated column for "Data3" using the following DAX formula:

DAXCopy code

Data3 = CALCULATE( MAX(YourTable[Data1]), FILTER( YourTable, YourTable[Data2] = EARLIER(YourTable[Data2]) ) )

This formula calculates the maximum value of "Data1" for each row in the table where "Data2" is equal to the current row's "Data2."

Here's a breakdown of the formula:

• CALCULATE: Modifies the context in which the formula is evaluated.
• MAX(YourTable[Data1]): Returns the maximum value of "Data1" within the specified context.
• FILTER: Restricts the context to rows that meet the specified conditions.
• YourTable[Data2] = EARLIER(YourTable[Data2]): Compares the current row's "Data2" value with the "Data2" values in the table, considering the context of the outer CALCULATE function.

  This way, the calculated column "Data3" will contain the corresponding "Data1" value for each row where "Data2" matches.

[MichalMIK]

Hi Thanks but It is not working quite good. I have recived:

while I'm expecting to recive this. This is of coures table to test.

Data 1	Data 2	Data3
1		3
2
3	1	6
4
5		9
6	3
7
8
9	5
10

[MichalMIK · Accepted solution]

I have made a little change and it works

[123abc]

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.