Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to build a report showing all items sold on orders with selected products. I have two tables. One has orders and th4e other has order details. I want to be able to select a single product from the details talbe then show all orders that had this item on them. I can show totals from the orders table but I also want to show the details ffrom the detaikls table but I don't want to limit the details to the select items. this is what I am talkign about.
Orders
OrderID Sales Tax
1 5.0 0.5
2 25.0 2.5
3 10.0 1.0
OrderDetails
OrderID ItemID Sales
1 a 2
1 b 3
2 a 2
2 c 23
3 b 3
3 c 7
If these are the two tables and I selet Item b
I want to return this data
OrderID Item Sales
1 a 2
1 b 3
3 b 3
3 c 7
The b4est way to describe this is a cosell report telling me what items are sold with a selected item. I can get the summary data from the roders table but I am unsure how to go back and get the details from those orders without havign it filtered by the selection.
Solved! Go to Solution.
Hi @Anonymous
Try the following:
1. Create a new one-column table, FilterTable, with all values of OrderDetails[ItemID] in FilterTable[ItemID]. Set FilterTable[ItemID] in a slicer. This will be used to select the the item of interest.
2. Place a table visual in your report and set in values OrderDetails[ItemID], OrderDetails[OrderID] and OrderDetails[Sales]. Choose "don't summarize" for all three.
3. Create this measure that will determine what items are to be shown:
ShowMeasure = VAR _OrdersList = CALCULATETABLE ( DISTINCT ( OrderDetails[OrderID] ); OrderDetails[ItemID] IN DISTINCT ( FilterTable[ItemID] ); ALL ( OrderDetails ) ) VAR _CurrentOrder = SELECTEDVALUE ( OrderDetails[OrderID] ) RETURN IF ( _CurrentOrder IN _OrdersList; 1 )
4. Place [ShowMesure] in the visual level filters of the table visual and select "Show values when the value is:" --> 1
@Anonymous
Hi, try with this
1. Create a new table
ItemsID=Values(OrdersDetail[ItemsID])
2. Create a measure:
FilterM = VAR _OrderID = SELECTEDVALUE ( OrderDetails[OrderID] ) RETURN IF ( COUNTROWS ( FILTER ( ALL ( OrderDetails ), OrderDetails[ItemID] IN VALUES ( ItemID[ItemID] ) && OrderDetails[OrderID] = _OrderID ) ) > 0, 1, BLANK () )
3. Put the measure in the Visual level filter and set to 1
Regards
Victor
Hi @Anonymous
Try the following:
1. Create a new one-column table, FilterTable, with all values of OrderDetails[ItemID] in FilterTable[ItemID]. Set FilterTable[ItemID] in a slicer. This will be used to select the the item of interest.
2. Place a table visual in your report and set in values OrderDetails[ItemID], OrderDetails[OrderID] and OrderDetails[Sales]. Choose "don't summarize" for all three.
3. Create this measure that will determine what items are to be shown:
ShowMeasure = VAR _OrdersList = CALCULATETABLE ( DISTINCT ( OrderDetails[OrderID] ); OrderDetails[ItemID] IN DISTINCT ( FilterTable[ItemID] ); ALL ( OrderDetails ) ) VAR _CurrentOrder = SELECTEDVALUE ( OrderDetails[OrderID] ) RETURN IF ( _CurrentOrder IN _OrdersList; 1 )
4. Place [ShowMesure] in the visual level filters of the table visual and select "Show values when the value is:" --> 1
Thanks this works
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |