cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AliNafa
Frequent Visitor

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

Hi everyone ,

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

ITEM_ID                         QTY            U_IDACTION_DATE       GROUP_IDCO_IDACC_ADMIN.ITEMSNewQtyForecastedSales
033-048-060-35780.00347Ahmed4/1/2019 17:3410085 0.003473.47
SP32370.004Ahmed4/1/2019 17:3210085 0.0044
SP22150.004Ahmed4/1/2019 17:3210085 0.0044
004-002-008-24590.5Ahmed4/1/2019 17:3410085 0.5500
019-35-005-10080.0416655Ahmed4/1/2019 17:2910085 0.041665541.6655
019-35-005-10080.0416655Ahmed4/1/2019 17:2910086 0.041665541.6655
033-048-060-35780.00347Ahmed4/1/2019 17:3410086 0.003473.47
SP32370.004Ahmed4/1/2019 17:3210086 0.0044
SP22150.004Ahmed4/1/2019 17:3210086 0.0044
004-002-008-24590.5Ahmed4/1/2019 17:3410086 0.5500
033-048-008-22520.00174Ahmed5/28/2019 9:3611035 0.001741.74
SP22150.0078Ahmed5/28/2019 9:3711035 0.00787.8
004-002-008-36821Ahmed5/28/2019 9:3611035 11000
011-037-020-39740.020833Ahmed5/28/2019 9:3611035 0.02083320.833
019-35-008-11030.020833Ahmed5/28/2019 9:3611035 0.02083320.833
033-048-008-22520.00174Ahmed5/28/2019 9:3611036 0.001741.74
SP22150.0078Ahmed5/28/2019 9:3711036 0.00787.8
004-002-008-36821Ahmed5/28/2019 9:3611036 11000
019-35-008-11030.020833Ahmed5/28/2019 9:3611036 0.02083320.833
011-037-020-39740.020833Ahmed5/28/2019 9:3611036 0.02083320.833
036-000-000-32070.001Ahmed9/26/2018 0:00SP22155 0.0040.004
031-45-000-31980.08Ahmed1/12/2019 17:34SP22155 0.0040.32
001-013-000-25660.15Ahmed9/26/2018 0:00SP22155 0.0040.6
031-65-000-31920.77Ahmed8/4/2021 9:25SP22155 0.0043.08
036-000-000-32070.001Ahmed9/26/2018 0:00SP22156 0.0040.004
031-65-000-31920.77Ahmed8/4/2021 9:25SP22156 0.0043.08
001-013-000-25660.15Ahmed9/26/2018 0:00SP22156 0.0040.6
031-45-000-31980.08Ahmed1/12/2019 17:34SP22156 0.0040.32
036-000-000-32070.001Ahmed9/26/2018 0:00SP32375 0.0040.004
031-44-000-31940.77Ahmed9/26/2018 0:00SP32375 0.0043.08
001-019-000-30550.15Ahmed9/26/2018 0:00SP32375 0.0040.6
031-45-000-31980.08Ahmed2/26/2019 17:13SP32375 0.0040.32
036-000-000-32070.001Ahmed9/26/2018 0:00SP32376 0.0040.004
031-44-000-31940.77Ahmed9/26/2018 0:00SP32376 0.0043.08
001-019-000-30550.15Ahmed9/26/2018 0:00SP32376 0.0040.6
031-45-000-31980.08Ahmed2/26/2019 17:13SP32376 0.0040.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_IDQTYU_IDACTION_DATEGROUP_IDCO_IDACC_ADMIN.ITEMSNewQtyForecastedSales
036-000-000-32070.001Ahmed26-09-2018 0:00SP22155 0.0040.004
031-45-000-31980.08Ahmed12/1/2019 17:34SP22155 0.0040.32
001-013-000-25660.15Ahmed26-09-2018 0:00SP22155 0.0040.6
031-65-000-31920.77Ahmed4/8/2021 9:25SP22155 0.0043.08
036-000-000-32070.001Ahmed26-09-2018 0:00SP22156 0.0040.004
031-65-000-31920.77Ahmed4/8/2021 9:25SP22156 0.0043.08
001-013-000-25660.15Ahmed26-09-2018 0:00SP22156 0.0040.6
031-45-000-31980.08Ahmed12/1/2019 17:34SP22156 0.0040.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 : 

1aaaa.jpg



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.

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png3.png2.png

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

View solution in original post

4 REPLIES 4
AliNafa
Frequent Visitor

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.


111555sss.jpg1115555aa.jpg

Thank you !

@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

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. 

sasd4454.jpg

 

aq7477.jpg

 

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
Super User
Super User

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

1.png3.png2.png

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors