I am trying to find a way to choose specific ID, have its values read and find all Products with the same name in different IDs and multiply the first ID X.values into a variable of the other products in different IDs.
so for example I have data below :
and I want to Choose ID = 1234 to find all the Volumes associated to the products of 1234. Find the products with the same name in other IDs and Multiply each Product volumes of ID=1234, into the same name product X.Value of the other ID, like Below :
Thanks
In a calculated column, you can use LOOKUPVALUE
to find the Volume for ID 1234 and the corresponding Product - then choose how to calculate your output based on whether a matching value was returned or not:
New Column =
VAR LookupID = 1234
VAR LookupVolume =
LOOKUPVALUE (
Table1[Volume],
Table1[ID], LookupID,
Table1[Product], Table1[Product]
)
RETURN
Table1[X.Value] & " * " &
IF (
ISBLANK ( LookupVolume ),
Table1[Volume],
LookupVolume
)
Worked example PBIX file: https://pwrbi.com/so_55916210/
More complex as a measure - not entirely clear how you intend using it, but this approach creates a separate table for a list of ID values, to be used as a slicer:
ID List = DISTINCT ( Table1[ID] )
Then we can use measure:
New Measure =
SUMX (
Table1,
VAR LookupID =
IF (
HASONEVALUE ( 'ID List'[ID] ),
VALUES ( 'ID List'[ID] ),
BLANK()
)
VAR LookupProduct =
IF (
HASONEVALUE ( Table1[Product] ),
VALUES ( Table1[Product] ),
BLANK()
)
VAR EffectiveVolume =
CALCULATE (
SUM ( Table1[Volume] ),
ALL ( Table1 ),
Table1[ID] = LookupID,
Table1[Product] = LookupProduct
)
RETURN
Table1[X.Value] *
IF (
ISBLANK ( EffectiveVolume ),
Table1[Volume],
EffectiveVolume
)
)
Updated PBIX file: https://pwrbi.com/so_55916210-2/