Custom Column based on Fact table condition

[elakkiyaselvan]

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.

[Ritaf1983 · Accepted solution]

Hi @elakkiyaselvan 

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 · Accepted solution]

Hi @elakkiyaselvan 

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

[elakkiyaselvan]

@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

[rajendraongole1 · Accepted solution]

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.

[ChoicePeace]

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