Forum Discussion
lookupvalue PBI
Table 1 Table 2
Region Code
US 1001 Estimate Week , AQ Week, Code Result
CHINA 1002 WK-01 WK-03 1002 WK-03
INDIA 1003 WK-01 WK-03 1001 WK-01
DENMARK 1004 WK-06 WK-03 1004 WK-06
both table have relationship based on code.
I need measure & calculated column (both needed) If table 2 - Code =1002 or China then AQ week else Estimate week
suggest DAX Formulas
Anonymous
you can try this
Column = if(RELATED('Table 1'[Region])="CHINA",'Table'[AQ Week],'Table'[Estimate Week])andMeasure = if(max('Table 1'[Region])="CHINA",max('Table'[AQ Week]),max('Table'[Estimate Week]))pls see the attachment below
10 Replies
Hey, Anonymous ,
if you really need calculated column, try this:
Result = IF ( Table2[Code] = 1002 || UPPER ( RELATED ( Table1[Region] ) ) = "CHINA", Table2[AQ Week], Table2[Estimate Week] )for measure:
Result Measure = VAR CodeSel = SELECTEDVALUE ( Table2[Code] ) VAR RegionSel = SELECTEDVALUE ( Table1[Region] ) VAR UseAQ = CodeSel = 1002 || UPPER ( RegionSel ) = "CHINA" VAR Result = IF ( UseAQ, SELECTEDVALUE ( Table2[AQ Week] ), SELECTEDVALUE ( Table2[Estimate Week] ) ) RETURN ResultFeel free to drop the UPPER.
Anonymous
I used OR to either filter by code or REgion, so dropp the scalar value filter and just filter based on Region. That's for the column.Result3 = IF ( RELATED ( Table1[Region] ) = "CHINA", Table2[AQ Week], Table2[Estimate Week] )It works as, it goes row by row, for each takes the code (due to relationship), checks in the related table and if for that paricular code the region is China, you get true/false.
Anonymous
you can try this
Column = if(RELATED('Table 1'[Region])="CHINA",'Table'[AQ Week],'Table'[Estimate Week])andMeasure = if(max('Table 1'[Region])="CHINA",max('Table'[AQ Week]),max('Table'[Estimate Week]))pls see the attachment belowHi,
For solving this question, what is the use of Table 1? Can't the calculated column result of Table2 be generated from Table 2 itself? In Table 2, write this calculated column formula
Result = if('Table 2'[Code] = 1002,'Table 2'[AQ Week],['Table 2'[Estimate Week])
Hope this helps.
Calculated Column (Table2):
Final Week =
IF (
RELATED ( Table1[Region] ) = "CHINA" || Table2[Code] = 1002,
Table2[AQ Week],
Table2[Estimate Week]
)
Measure:Final Week =
IF (
SELECTEDVALUE ( Table1[Region] ) = "CHINA" || SELECTEDVALUE ( Table2[Code] ) = 1002,
SELECTEDVALUE ( Table2[AQ Week] ),
SELECTEDVALUE ( Table2[Estimate Week] )
)Hi Anonymous,
I would also take a moment to thank Shahid12523 , Ashish_Mathur, ryan_mayu, vojtechsima for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Best Regards,
Harshitha.Hi Anonymous,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Harshitha.Hi Anonymous,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.
Regards,
Harshitha.
Hi Anonymous
You can achieve this both ways — with a calculated column (row-level logic in Table 2) and a measure (aggregate, context-based). Since your relationship is on Code, you can use either RELATED or LOOKUPVALUE to bring in the Region from Table 1 and then apply the condition.
Calculated Column in Table 2:
FinalWeek_Column = VAR RegionName = RELATED ( Table1[Region] ) RETURN IF ( OR ( Table2[Code] = 1002, RegionName = "CHINA" ), Table2[AQ Week], Table2[Estimate Week] )This will assign AQ Week if the code is 1002 or the region is China, otherwise Estimate Week.
Measure:
FinalWeek_Measure = VAR RegionName = SELECTEDVALUE ( Table1[Region] ) VAR CodeValue = SELECTEDVALUE ( Table2[Code] ) RETURN IF ( OR ( CodeValue = 1002, RegionName = "CHINA" ), SELECTEDVALUE ( Table2[AQ Week] ), SELECTEDVALUE ( Table2[Estimate Week] ) )
