Forum Discussion
Custom Column based on Fact table condition
Hello All,
I'm looking for a solution to the following scenario: I want to create a new custom column in a dim table based on a condition applied to a fact table.
| Dim | |
| ID | Name |
| 1 | Direct |
| 2 | Direct |
| 3 | Indirect |
| 4 | Indirect |
| Fact | |
| ID | Indicator |
| 1 | N |
| 2 | N |
| 3 | Y |
| 4 | N |
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 |
| 4 | Indirect | Indirect | If Dim(InDirect) = Fact(InDirect) & Fact Indicator = N then InDirect |
Many thanks in advance.
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.
4 Replies
Hi,
You can use the function switch, and it would be more optimiser then IF.
https://learn.microsoft.com/en-us/dax/switch-function-dax
don't hesitate to see the examples
Best regards,
ChoicePeace
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
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 QDP3 Indirect Indirect If Dim(InDirect) = Fact(InDirect) & Fact Indicator = N
then InDirect4 Indirect Indirect If Dim(InDirect) = Fact(InDirect) & Fact Indicator = N
then InDirectHi 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.
