Forum Discussion
Helper ICombine values from visuals into a total visual
mahoneypat Anonymous
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!
Helper IAnonymous 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.
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 Anonymous , 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.
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
Anonymous 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.