I have one MainTable in Power BI named as Master(Source: Aws:postgresql database).
Based on this table i have created one summarized table with some required columns. Both table have common id colum as IDNo (Which is not unique multiple row items are available). I have created some calculated columns in 2nd table (Agent1 tablename)
From Maintable 2nd table was created with below formula
Agent1 = SUMMARIZE( FILTER( ALLNOBLANKROW(MainTable), [headermain]="No" && [NONIFCODE_V3]<>BLANK() && ([STATUS] = "HELD" || [STATUS] = "ORDER" || [STATUS] = "LOST") ), MainTable[IDNo], MainTable[NONIFCODE_V3], MainTable[noniftccode], MainTable[codecusscreen], MainTable[codeorderscreen], "CountPerMantacause", CALCULATE(COUNTROWS(MainTable)) ) used to create table
Then later created certain calculated columns in Agent1 table as
Ranking = Agent1 [CountPerMantaCause]*100+Agent1 [PriorityRanking]
PriorityRanking = IF(ISBLANK([NONIFCODE_V3]),BLANK(),LOOKUPVALUE(PriorityTableCC[Ranking_C1],
PriorityTableCC[Causecode],[NONIFCODE_V3]))
RankingRemark = IF(Agent1 [Ranking]=Agent1 [MaxRankingTable],"Keep",BLANK())
MaxRankingTable = CALCULATE(MAX(Agent1 [Ranking]),ALLEXCEPT(Agent1 ,Agent1 [mantaorderno]))
RankingRemarkFIltered =IF(Agent1 [RankingRemark]="Keep","Keep", BLANK())
FinalCCHEADER = IF(Agent1 [RankingRemarkFIltered]="Keep",Agent1 [mantaorderno],BLANK())
CC_CustscreenTemp =IF(ISBLANK(Agent1 [codecusscreen])||Agent1 [codecusscreen]="Blank",
Agent1 [codeorderscreen],Agent1 [codecusscreen])
NONCCFINAL_CC =IF((ISBLANK(Agent1 [noniftccode])||Agent1 [noniftccode]="Blank")
&& NOT ISBLANK(Agent1 [FinalCCHEADER]),Agent1 [NONIFCODE_V3],Agent1 [noniftccode])
After all this calculated columns unable to create direct relationship between these two table Maintable & Agent1
hence created UniqueIDNum table with this formula from MainTable
UniqueNo= DISTINCT(VALUES(MainTable[IDNo])) later relationship created between these 3 tables based on IDNO as Many to one relationship and cross filter direction as single.
Later I want to fetch this Agent1 calculated column to MainTable later supposed to apply in another column in this MainTable
FinalCCHEADER1_MainTable =
VAR MIDNo = RELATED(UniqueIDNum [UniqueNo])
VAR LookupResult =MAXX(ALL(Agent1),IF( Agent1[FinalCCHEADER]= MIDNo ,Agent1[NONIFCODE_V3],BLANK()))
RETURN
IF (NOT ISBLANK ( LookupResult ),FORMAT ( LookupResult, "0" ),BLANK())
After this calculated column another calculated column created with this formula as NONIFCODE_V3
CCHEADER_FINAL_V1 =
IF(MainTable[noniftccode]="Blank"||MainTable[noniftccode]=BLANK(),MainTable[Final_NONIFOTCAUSECODE_HEADER1],
CALCULATE(VALUES(MainTable[noniftccode]),REMOVEFILTERS(MainTable[FinalCCHEADER1_MainTable])))
After that finally CCHEADER_FINAL_V1 column has to be applied in one calculated column Case_10 where noniftccode value has to be replaced with these CCHEADER_FINAL_V1 column but I am getting circular dependancy error.
Same like that RC Agent2 table and columns also created like replica CC.
But I want to fix this circular dependancy error tried in all the extent. How to resolve this error
