The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to Solution.
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.
Hope this helps.
Proud to be a Super User! | |
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 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.
Proud to be a Super User! | |
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