Inserting values from one table to another table based on the selection of slicers

[thbaero]

Hi all,

I have one table that contains values that I calculate every week.

Basis ist a long list of partnumbers per Team where every part is categorized by "BaseType".

I summarize this list every week and get the count of values for "I", for "I"+"N" and for "I"+"N"+"V"

table: I_N_V_Values

ExportDate	SE-Team	Team	BaseType	BaseDate	BaseValue
13.04.2020	SE-Team 4	590	I	14.01.2019	13
13.04.2020	SE-Team 4	590	IN	08.04.2019	68
13.04.2020	SE-Team 4	590	INV	25.05.2020	98
06.04.2020	SE-Team 4	590	I	14.01.2019	13
06.04.2020	SE-Team 4	590	IN	08.04.2019	67
06.04.2020	SE-Team 4	590	INV	25.05.2020	99
...
19.11.2018	SE-Team 4	590	I	14.01.2019	13
19.11.2018	SE-Team 4	590	IN	08.04.2019	22
19.11.2018	SE-Team 4	590	INV	25.05.2020	25
...
...	SE Team 4	591	...	...	...
...	SE Team 1	123	...	...	...

I also have another list with a timeseries starting from 19.11.2018 until 29.06.2020 with an interval of 7 days.

Whenever there is a date that fits to one of the dates from "I", "IN" or "INV" I already list that information in the table.

table:BurnUpCurve

Date	BaseType
19.11.2018
26.11.2018
...
14.01.2019	I
...
08.04.2019	IN
...
25.05.2020	INV

I also have three slicers to select an specific "ExportDate", "SE-Team" and "Team".

Based on the data of table: I_N_V_Values and the selection of the slicers I want to add columns to table: BurnUpCurve.

e.g. Slicers are:

ExportDate = 13.04.2020

SE Team = SE Team 4

Team = 590

table:BurnUpCurve

Date	BaseType	BaseValue
19.11.2018
26.11.2018
...
14.01.2019	I	13
...
08.04.2019	IN	68
...
25.05.2020	INV	98

Later I will add another column to the tabel "BurnUpCurve" where I am going to interpolate the values inbetween to get all datapoints for a line graph.

I already looked through a lot of solutions that were able to get the value of a slicer and to use it in DAX for further calcuations.

However, I was not able to use the value of a slice as an input to filter for the right value in table:I_N_V_Values

I tried for example:

BaseValue = 
    var Select_Slicer = SELECTEDVALUE(dimDate[Value].[Date])
RETURN
    SUMX(filter('I_N_V_Values';'I_N_V_Values'[BaseType] = BurnUpCurve[BaseType] && 'I_N_V_Values'[ExportDate] = Select_Slicer);'I_N_V-Value'[BaseValue])

However the formula above didn't work.

I'd be really happy if somebody could point me towards a solution for my problem.

[v-zhenbw-msft]

Hi @thbaero ,

Sorry for that but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically.

We can create a measure and the relationship between tables to meet your requirement.

1. Create relationships between your tables,

2. Then create a simple measure like this,

BaseValue = CALCULATE(SUM(I_N_V_Values[BaseValue]))

3. And use I_N_V_Values[BaseDate], BurnUpCurve[BaseType] and [BaseValue] measure to create a table visual. We can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thbaero]

Hi @_ zhenbw

thank you for the quick reply. But currently I am in the middle of a huge "chaos" in my head.

Still trying to figure out the logic behind some of the DAX concepts (and going through the guided learning for that matter).

I already moved on a little bit on in my train of thoughts.

I will still need some time to really think about my problem in order to be able to describe it.

Enclosed you can also find a set of data I sampled and anonymized from the real data.

https://drive.google.com/open?id=1PNiHrgyjOQgh3J0KXhO7uVSERILLyo5M

Basically I am having a set of data from a database that is exported once a week (ExportDate)

The data is a list of elements which are categorized by

• Team (Team 1, Team 2, ...., )
• BaseName (I, N, V)
• ElementStatus (open, in progress, done)

For the BaseNames there is a second table that holds specifc dates for each distinct BaseName

Base Name	Week
Start	44/2019
I	51/2019
N	09/2020
V	22/2020
End	27/2020

(Actually those dates are the Mondays of each week but the picture below only shows the weeks)

I want to generate a combined stacked column and line chart from this data:

1. The stacked column chart should show count of elements grouped by ElementStatus over time (ie ExportDates)
2. The line graph should display the cumulative amount of elements on a specific ExportDate (selected by a slicer) matched to the specific date of the BaseName.
3. I want to be able to select a team on a slicer and get the data for that team.
   If I don't select a team than the cumulative Sums over all teams should be displayed.

Many thanks for the help!

Please be aware that I am currently using MS Power BI (September 2019) and apparently PBIX-Files from the current version are sometimes not compatible with my version.