topic "power bi" inactive relation for two fact tables same column (order_id) in DAX Commands and Tips

"power bi" inactive relation for two fact tables same column (order_id)

perezco — Wed, 17 Apr 2024 14:14:49 GMT

under table1 ('Fact - order') I tried create the below column

Column order = CALCULATETABLE( LASTNONBLANK('Fact - order'[order_wid],'Fact - order'[order_wid]),USERELATIONSHIP('Fact - order'[order_wid],'Fact - order Parts'[order_wid]))

two fact order tables (relationship is inactive but 1 --->> *, I need 1 <<---* because my intention is that any part is been choosen in table two will brings the orders from table 1 and their commodity, is this possible?

table one table two
order_ID - commodities 1<<---need be----* order_id -parts# - partsQties
(unique values) (many)

----extra

the commodity brings somehing like this
Hard Drive-XXW34 :(2)

Re: "power bi" inactive relation for two fact tables same column (order_id)

Cperota — Wed, 17 Apr 2024 02:31:50 GMT

did you try lookup function ?>> The lookup function is a good option when you need a single column.
https://www.sqlbi.com/articles/lookup-multiple-values-in-dax/

Re: "power bi" inactive relation for two fact tables same column (order_id)

Anonymous — Wed, 17 Apr 2024 02:57:18 GMT

Hi Cperota ,thanks for the quick reply.

Hi perezco ,

Regarding your question, I made an attempt.

The Table data is shown below:

Please follow these steps:

1.Modify the filter passing direction

2.Use the following DAX expression to create a measure

Measure = CALCULATE(SELECTEDVALUE('Fact - order'[Product]),USERELATIONSHIP('Fact - order Parts'[order_wid],'Fact - order'[order_wid]))

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: "power bi" inactive relation for two fact tables same column (order_id)

perezco — Wed, 17 Apr 2024 16:28:29 GMT

thanks, Cperota.

The table 2 'Fact - order Parts' is the one that need to get the column string values 'commodity description' from table 1 'Fact - order'.

To avoid inaccurate calculations, I am trying to do it without USERELATIONSHIP function active /not active stage.

Column (inactive) =

ADDCOLUMNS (
'Fact - order Parts',
"commodity_desc",
VAR order_wid_ =
'Fact - order Parts'[order_wid] //----->my issue here - I have mutiple wo_id
RETURN
LOOKUPVALUE (
'Fact - order'[commodity_desc],
'Fact - order'[order_wid], 'Fact - order Parts'[order_wid],
'Fact - order'[order_wid], order_wid_
) ,
"TREATAS connection",
VAR A_treatas =
TREATAS( VALUES('Fact - order'[order_wid]), 'Fact - order Parts'[order_wid])
RETURN
A_treatas
)

-------------------------------------------

note: I am having proble to answer Anonymous . I dont understand my message keep be deleted it.