Forum Discussion
Helper ICombine values from visuals into a total visual
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
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!
9 Replies
- mahoneypat
Microsoft Employee
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
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.
SearchKnowledgeAnonymous 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.
- SearchKnowledge
Anyone else that has a good idea?
- SearchKnowledge
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!
