Forum Discussion
Custom Column based on Fact table condition
Since you're pulling a value from the Fact table (many-side) into the Dim table (one-side), you must apply an aggregation, like MAXX, MINX, or SELECTEDVALUE. Without it, the engine won’t know which value to return when multiple rows exist.
So yes — your logic is fully achievable as long as the Indicator column in the Fact table has at most one value per ID.
Here’s a DAX expression that does exactly what you need:
Custom =
var from_Fact = MAXX(RELATEDTABLE('Fact'),'Fact'[Indicator])
RETURN
IF('Dim'[Name]="Direct","Direct",
IF('Dim'[Name]="Indirect" && from_Fact="Y","QDP",
"Indirect")
)The pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi elakkiyaselvan - This cannot be done with a calculated column dim table which only produces 1 row per key.Please check the below logic using calculated table.
DimFact Expanded =FILTER (ADDCOLUMNS (SELECTCOLUMNS (CROSSJOIN ( 'Dim', 'Fact'),"DimID", Dim[ID],"DimName", Dim[Name],"FactID", Fact[ID],"Indicator", Fact[Indicator]),"Custom Column",SWITCH (TRUE(),[DimName] = "Direct" && [DimID] = [FactID], "Direct",[DimName] = "Indirect" && [DimID] = [FactID] && [Indicator] = "Y", "QDP",[DimName] = "Indirect" && [DimID] = [FactID] && [Indicator] = "N", "Indirect",BLANK())),NOT ISBLANK([Custom Column]))Hope this helps.
Ritaf1983 Thank you for your assistance. Need your assistance on below output as well. I have added additional condition for 3.Incase if have both Indicator for 3 (If 3 = Y QDP, 3 = N Indirect)
Excepted Output
| DIM | |||
| ID | Name | Custom Column | Condition |
| 1 | Direct | Direct | If Dim(Direct) = Fact(Direct) then Direct |
| 2 | Direct | Direct | If Dim(Direct) = Fact(Direct) then Direct |
| 3 | Indirect | QDP | If Dim(InDirect) = Fact(InDirect) & Fact Indicator = Y then QDP |
| 3 | Indirect | Indirect | If Dim(InDirect) = Fact(InDirect) & Fact Indicator = N then InDirect |
| 4 | Indirect | Indirect | If Dim(InDirect) = Fact(InDirect) & Fact Indicator = N then InDirect |
Hi elakkiyaselvan - This cannot be done with a calculated column dim table which only produces 1 row per key.Please check the below logic using calculated table.
Hope this helps.