Duplicate Values Based from another table

[devgian]

Hi Everyone, 

Good day! 

I would like to ask if there's a way to duplicate row values based on selected value. 

Example: 
I have table below with fruit value apple.

ID	Fruit	FY
1	apple	SY2

I have a second table below that has empty value.

ID	Fruit	FY

What i want to happen is when apple is selected, it will automatically creates data in the second table as duplicate value to first table. 

Expected result something below: 

ID	Fruit	FY
1	apple	SY2
1	apple	SY1
1	apple	SY3

Also, if nothing is selected from main table it will automatically clear the data from second table. Is this possible in POWER BI? 

Thanks in advance.

[Anonymous]

Hi @devgian ,

In Power BI, you can achieve the behavior you described using DAX measures and slicers. You can create a slicer to select the fruit value from the first table, and based on the selected fruit, you can generate rows in the second table using DAX measures. However, Power BI does not have the ability to directly modify or insert rows into tables in the same way a database can. Instead, you can create calculated tables or generate data within visuals.

Here's a general outline of how you can achieve the behavior you described:

1. Create a Slicer:

   • Add a slicer visual to your report based on the "Fruit" column from the first table. Users can select a fruit from this slicer.

2. Create a Measure to Filter Data:

   • Create a DAX measure that calculates the selected fruit based on the slicer. For example:
   DAX

• SelectedFruit = SELECTEDVALUE('First Table'[Fruit])

• Generate Rows in a Table:

  • You can create a calculated table or use DAX measures to generate rows in the second table based on the selected fruit. Here's an example measure to generate rows:
  DAX

1. SecondTable = VAR SelectedFruit = [SelectedFruit] RETURN IF(ISBLANK(SelectedFruit), BLANK(), ADDCOLUMNS( FILTER('Second Table', 'Second Table'[Fruit] = SelectedFruit), "ID", 1, // Adjust the ID as needed "Fruit", SelectedFruit, "FY", "SY1" // FY value can be customized ) )

   This measure generates rows in the "Second Table" based on the selected fruit. If nothing is selected (blank), it returns a blank result.

2. Visualize the Second Table:

   • Use a table visual in your report that displays the "Second Table."

3. Clear Data When Nothing is Selected:

   • By design, when nothing is selected in the slicer, the "Second Table" will display as blank. You don't need a specific action to clear the data.

With this setup, when a user selects a fruit in the slicer, the "Second Table" will display the corresponding rows. If nothing is selected, the "Second Table" will be blank.