Combine values from visuals into a total visual

[SearchKnowledge]

Hello kind reader,

The situation is like this:

I have two table visuals: Bothe tables contain different columns and different date filters, however they have 1 column which is the same: The product column. So why are the tables seperate you might ask, because: 1 table is filtered on a specific date and the other visual filtered on another date, these dates need to be flexible and can change. In the end, I want to combine the results from these two visuals into one. How can 

[SearchKnowledge · Accepted solution]

@mahoneypat @v-rzhou-msft 
I have found a solution: It was to put the function ALL('Date') into the measures i didnt want to be affected by the slicer. And then with Daterange() i could set the static date range. 

Thanks you all for thinking with me!

[SearchKnowledge]

Anyone else that has a good idea?

[SearchKnowledge]

@v-rzhou-msft  and @mahoneypat 

Thank you guys for helping me, very much appreciated. I tried both solutions but it didnt quite work or maybe i didnt fully understand them. So let me explain a little bit more.

The above screenshot shows the exact situation. I have 3 tables. The end result should be the 3e table (without column 5), i added it in table 3 to show you the problem. Column number 6 is calculated by substracting column 5 from column 3. However, i want it to be that column 6 is calculated by substracting column 4 from 3.

As you can see there is a date slicer which only applies to the second table, as it should be. This column should have a variable date, however the other columns should have a static date. This means that the total value in the 3e table is a combination of a variable date measure and a static date measure. I cannot apply the slicer to the 3e table because then it will also have affect on the inventory. I hope this helps explaining the situation. Any idea? i appreciate you guys help very much.

[v-rzhou-msft]

Hi @SearchKnowledge 

I think you want to get the result(measure6) by substracting measure3 and measure4.

Due to I don't know your data model, I build a sample to have a test.

Table1:

Table2:

I build two matrix visuals by two tables and filter two visual by the date column in two tables individually.

Measure:

3 = SUM(Table1[Aan])+SUM(Table1[Inkoop])

4 = SUM(Table2[D O])

Measure = CALCULATE([3],FILTER(Table1,Table1[Level1]=MAX(Table2[Level1])&&Table1[Level2]=MAX(Table2[Level2])))
-[4]

6 = IF(ISINSCOPE(Table2[Level2]),[Measure],SUMX(FILTER(Table2,Table2[Level1]=MAX(Table2[Level1])&&Table2[Level2]=MAX(Table2[Level2])),[Measure]))

Table2Filter = IF(MAX(Table1[Level1]) in VALUES(Table2[Level1]),1,0)

Build a new matrix visual by level1,level2 in Table2 and measure6 .

Then add Table2Filter into the filter fields in this matrix and set this measure to show items when value =1.

Result is as below.

You see Measure6 = measure3- measure4. Measure3 and measure4 are filtered by different date slicer.

You can download the pbix file from this link: File

Best Regards,

Rico Zhou

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

[SearchKnowledge]

Hi @v-rzhou-msft , thank you a lot for you effort. However, I do not fully understand what you have done or trying to do. Therefore i dont know how to explain it more deeply or better. But as you can see in the screenshot above it is not really the result that i need.

[v-rzhou-msft]

Hi @SearchKnowledge 

I thought your requirement was to build a new table visual show results by column 6 which was calculated by substracting column 4 from 3.

You may get column3 by sum Aan and Inkoop in Table1.

Do you want to keep the filter on column3 and column4 then substracting column 4 from 3?

I filter column3 by Date(2020/10/31), filter column4 by Date(2020/10/31 - 2021/1/18) like your screenshot.

I am confused about column5 and your column6.

And 4411(column3) - 370(column4) does not equal to 4008(column6).

I may have some misunderstand of your requirement. Please show me more details about your calculated logic. And you can show me the result you want. This may make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

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

[SearchKnowledge]

@v-rzhou-msft First of all, thank you so much for the effort.

Well my requirement is to get the result of table one and table two combined into table 3 ( you can forget about colum 5, just ignore it). So in my example the result in column 3 should be 15110 - 1377 = 13.773.  The tricky part is that the values 15110 and 1377 are influenced by different date ranges. So table one and two are correct as they are, i just cant combine them into table 3. When i try to make any Measure it seems that it cant refer to any visual.

[v-rzhou-msft]

Hi @SearchKnowledge 

I think you want to filter two tables firstly and then append two tables to get a new table.

Do you want to build a calculated table? You can try slicer and union to get the result by dax.

Or you can try parameter and append to get result by Power Query.

However you can't get a dynamic table filtered by slicer or parameter.

If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business. And you can show me a screenshot of the result you want.

Best Regards,

Rico Zhou

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

[mahoneypat]

You can harvest to two filter dates in variables in a new measure and then use them both in the filter of the calculation.  For example,

NewMeasures = var vSlicer1Date = SELECTEDVALUE(Slicer1[Date])

var vSlicer2Date = SELECTEDVALUE(Slicer2[Date])

return CALCULATE([YourMeasure], Table[Date] = vSlicer1Date || Table[Date] = vSlicer2Date)

Regards,

Pat