Dispaly results for an item linked it's componentes on another columns

[AliNafa]

Hi everyone ,

I need some help to write DAX command , I have this table shown below :

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID	ACC_ADMIN.ITEMS	NewQty	ForecastedSales
033-048-060-3578	0.00347	Ahmed	4/1/2019 17:34	1008	5		0.00347	3.47
SP3237	0.004	Ahmed	4/1/2019 17:32	1008	5		0.004	4
SP2215	0.004	Ahmed	4/1/2019 17:32	1008	5		0.004	4
004-002-008-2459	0.5	Ahmed	4/1/2019 17:34	1008	5		0.5	500
019-35-005-1008	0.0416655	Ahmed	4/1/2019 17:29	1008	5		0.0416655	41.6655
019-35-005-1008	0.0416655	Ahmed	4/1/2019 17:29	1008	6		0.0416655	41.6655
033-048-060-3578	0.00347	Ahmed	4/1/2019 17:34	1008	6		0.00347	3.47
SP3237	0.004	Ahmed	4/1/2019 17:32	1008	6		0.004	4
SP2215	0.004	Ahmed	4/1/2019 17:32	1008	6		0.004	4
004-002-008-2459	0.5	Ahmed	4/1/2019 17:34	1008	6		0.5	500
033-048-008-2252	0.00174	Ahmed	5/28/2019 9:36	1103	5		0.00174	1.74
SP2215	0.0078	Ahmed	5/28/2019 9:37	1103	5		0.0078	7.8
004-002-008-3682	1	Ahmed	5/28/2019 9:36	1103	5		1	1000
011-037-020-3974	0.020833	Ahmed	5/28/2019 9:36	1103	5		0.020833	20.833
019-35-008-1103	0.020833	Ahmed	5/28/2019 9:36	1103	5		0.020833	20.833
033-048-008-2252	0.00174	Ahmed	5/28/2019 9:36	1103	6		0.00174	1.74
SP2215	0.0078	Ahmed	5/28/2019 9:37	1103	6		0.0078	7.8
004-002-008-3682	1	Ahmed	5/28/2019 9:36	1103	6		1	1000
019-35-008-1103	0.020833	Ahmed	5/28/2019 9:36	1103	6		0.020833	20.833
011-037-020-3974	0.020833	Ahmed	5/28/2019 9:36	1103	6		0.020833	20.833
036-000-000-3207	0.001	Ahmed	9/26/2018 0:00	SP2215	5		0.004	0.004
031-45-000-3198	0.08	Ahmed	1/12/2019 17:34	SP2215	5		0.004	0.32
001-013-000-2566	0.15	Ahmed	9/26/2018 0:00	SP2215	5		0.004	0.6
031-65-000-3192	0.77	Ahmed	8/4/2021 9:25	SP2215	5		0.004	3.08
036-000-000-3207	0.001	Ahmed	9/26/2018 0:00	SP2215	6		0.004	0.004
031-65-000-3192	0.77	Ahmed	8/4/2021 9:25	SP2215	6		0.004	3.08
001-013-000-2566	0.15	Ahmed	9/26/2018 0:00	SP2215	6		0.004	0.6
031-45-000-3198	0.08	Ahmed	1/12/2019 17:34	SP2215	6		0.004	0.32
036-000-000-3207	0.001	Ahmed	9/26/2018 0:00	SP3237	5		0.004	0.004
031-44-000-3194	0.77	Ahmed	9/26/2018 0:00	SP3237	5		0.004	3.08
001-019-000-3055	0.15	Ahmed	9/26/2018 0:00	SP3237	5		0.004	0.6
031-45-000-3198	0.08	Ahmed	2/26/2019 17:13	SP3237	5		0.004	0.32
036-000-000-3207	0.001	Ahmed	9/26/2018 0:00	SP3237	6		0.004	0.004
031-44-000-3194	0.77	Ahmed	9/26/2018 0:00	SP3237	6		0.004	3.08
001-019-000-3055	0.15	Ahmed	9/26/2018 0:00	SP3237	6		0.004	0.6
031-45-000-3198	0.08	Ahmed	2/26/2019 17:13	SP3237	6		0.004	0.32

The GROUP_ID which is ( 1008 ) linked with several ITEM_ID and one of the ITEM_ID ( SP2215 ) Can be also a GROUP_ID and have another ITEM_ID inside it.

ITEM_ID	QTY	U_ID	ACTION_DATE	GROUP_ID	CO_ID	ACC_ADMIN.ITEMS	NewQty	ForecastedSales
036-000-000-3207	0.001	Ahmed	26-09-2018 0:00	SP2215	5		0.004	0.004
031-45-000-3198	0.08	Ahmed	12/1/2019 17:34	SP2215	5		0.004	0.32
001-013-000-2566	0.15	Ahmed	26-09-2018 0:00	SP2215	5		0.004	0.6
031-65-000-3192	0.77	Ahmed	4/8/2021 9:25	SP2215	5		0.004	3.08
036-000-000-3207	0.001	Ahmed	26-09-2018 0:00	SP2215	6		0.004	0.004
031-65-000-3192	0.77	Ahmed	4/8/2021 9:25	SP2215	6		0.004	3.08
001-013-000-2566	0.15	Ahmed	26-09-2018 0:00	SP2215	6		0.004	0.6
031-45-000-3198	0.08	Ahmed	12/1/2019 17:34	SP2215	6		0.004	0.32

So it's like a Tree.

( GROUP_ID which is1008 ) have an items started with numbers + one SP item which also contains items inside it )

The main issue is that the ( SP2215 ) ITEM_ID can be linked with more than one ( GROUP_ID ) , if you see on the table it's linked with item ID 1008 and 1103 and i tried to make it but i got an error says there's duplication.

on the picture below , When i click on item 1008, it should show the ITEMS_ID related wiht it and if there was an SP or any item starts with a latter as ab ITEM_ID that means the SP can be also a GROUP_ID and it should show the items for it too on the Dashboard

Expected result is shown on the Picture below : 

 

PPIX file on the link below :

https://drive.google.com/file/d/1nKQrjZVeRrEVF9wdUR-5QCDrmgrfy8CQ/view?usp=sharing

Excel File :

https://docs.google.com/spreadsheets/d/1OJdfyjPgu--9xOp6om5xE6emY7mqn18g/edit?usp=sharing&ouid=10354...

Thank you.

[AliNafa]

Hi @tamerj1  ,

What you have done is really a great and that's what i was looking for .

But can i added the results on the report without filtering on a column like the picture below ? I hope you can help me on this too.




Thank you !

[tamerj1]

@AliNafa 

Please try

FilterMeasure =
VAR CurrentRecordGroup =
SELECTEDVALUE ( Sheet1[GROUP_ID] )
VAR SelectedGroups =
VALUES ( Groups[GROUP_ID] )
VAR Items =
CALCULATETABLE (
VALUES ( Sheet1[ITEM_ID] ),
Sheet1[GROUP_ID] IN SelectedGroups,
ALLEXCEPT ( Sheet1, Sheet1[Parent] )
)
VAR FilteredItems =
FILTER ( Items, CONTAINSSTRING ( Sheet1[ITEM_ID], "SP" ) )
VAR GroupsToDisplay =
UNION ( SelectedGroups, FilteredItems )
VAR Result =
IF ( CurrentRecordGroup IN GroupsToDisplay, 1 )
RETURN
Result

[AliNafa]

Hi @tamerj1 

Thank you for your quick response. Really appreciate it.

The code that you have wrote is still will make it as a filtering on Dashboard only. But i need to make it as a report as on the picture below without the filtering. Or we may add it as a column on the table. 

Just thought that we may add the GROUP_ID_2 column here on the Table

So for example , GROUP_ID 1008 is linked with different ITM_IDs and two of them are SP3237 and SP2215. And also SP3237 and SP2215 can become GROUP_ID and contain ITEMS under them.

So, on the new column , I just would like to have the group id which is 1008 that is linked with ALL the item IDs. The reason i'm doing this is because I would like to see all the items under 1008 that should be available in order to produce the final product which is Item 1008.

I hope you apply the solution on the PPIX file attached and send it to me if possible.

https://drive.google.com/file/d/1nKQrjZVeRrEVF9wdUR-5QCDrmgrfy8CQ/view?usp=sharing

Thank you So much !

[tamerj1 · Accepted solution]

Hi @AliNafa 
Please see attached your sample file with the solution

Groups = 
FILTER ( 
    ALLNOBLANKROW ( Sheet1[GROUP_ID] ),
    Sheet1[GROUP_ID] <> BLANK ( )
)

FilterMeasure = 
VAR CurrentRecordGroup = SELECTEDVALUE ( Sheet1[GROUP_ID] )
VAR SelectedGroups = VALUES ( Groups[GROUP_ID] )
VAR Items = CALCULATETABLE ( VALUES ( Sheet1[ITEM_ID] ), Sheet1[GROUP_ID] IN SelectedGroups, ALL ( Sheet1 ) )
VAR FilteredItems = FILTER ( Items, CONTAINSSTRING ( Sheet1[ITEM_ID], "SP" ) )
VAR GroupsToDisplay = UNION ( SelectedGroups, FilteredItems )
VAR Result = IF ( CurrentRecordGroup IN GroupsToDisplay, 1 )
RETURN
    Result