Forum Discussion
Calculate a measure using 2 different selections from the same table as filters
I am in a pickle here... someone handed this to me and said solve this or else... Ive been trying to figure this out for the past day or so I don't know if this is even possible, so here is goes.
Here is my data model, nothing fancy so far.
Here is my Report page/dash
The selected card ("nova marg%") has the following calculation, all the measures are calculated with values from "Fvendas"
However, as it is, the value returned is wrong. I've been messing with "edit interactions" and for each single value it partially works (before the final calculation). If i set the card to be filtered by the green slicer only [precoreco] and [ajusteimp] turn out right, however [cstunit] value is wrong. And if I set the card to filtered by the blue slicer only it is the other way around.
And if filtered by both it just crashes I think it is because they are, after all, on the same table and column (Dprodutos[item_desc]) so it cannot be filtered based on a selection from the same column twice. They only show different values because they have a filter applied to them based on the column Dprodutos[mp] , if it is set to 1 it returns the blue sclicer values and if mp = 0 it returns the values shown on the green slicer.
Here goes my Dprod table for context:
I have been looking into SEECTEDVALUE() to use together with CALCULATE(), nort sure even If it works or how to make this work with 2 different selections.
To get the value that I need, [Preço Recomendado] and [AjusteImp] must be filtered using the value selected from the green slicer to the right while [CstUnit] must be filtered based on the value on the blue slicer. Is there a way to do so? even if i need to change my data structure or w/e.
Thanks in advance
Hi Anonymous ,
The method you used before is correct. Actually I mean you could create a calculated table via ALLSELECTED() not getting the selected value in slicer like this:
New Table=DISTINCT(ALLSELECTED('MP'[Desc],MP[VALOR]))Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
I figured it out , however there is an extra step that I would like to avoid so I want some input from you guys.
Simplyfing things here is the mock up of my previous model.
What I wanted to do was make the card in the middle be filtered by the selection from both slicers (OL to the right and MP to the left).
But why so ? because inside the measure used for the card contains 2 variables and each of them needed to be filtered by one, but not the other slicer. For instance, the mock up measure i used was :
CONTA =VAR FATOROL = VENDA[SALE] //this one was to be filtered by the OL slicer onlyVAR FATORMP = VENDA[SALE] // this one was to be filtered by the MP sclicer onlyRETURNFATOROL/FATORMPSo when I selected a value from both slicers it would give me a measure that was the result from a combination of the selection from both slicers.
However both slicers where filtering the same column on the same table (they were both set from MP[descrição], and no, using multiple selections would not solve my issue) So in short what i needed was for the variables of a measure to be filtered based on multiple selections from the same column.
Which I could not make it work... so to fix this I duplicated the OL table, called it MP, changed the data structure to this :
And changed the measure to
CONTA =VAR FATOROL = CALCULATE(VENDA[SALE],REMOVEFILTERS(MP[Descrição]))VAR FATORMP = CALCULATE(VENDA[SALE],REMOVEFILTERS(OL[dESCRIÇÃO]))RETURNFATOROL/FATORMPSo the first variable ignores the selection from the MP table and the second ignores the OL table. And now it works !!!!
But I had to duplicate an entire table to do this ... Now my question is... is there any fancier way of achieving the same result without dupplicating the table and using a 1:1 relationship?
Hi Anonymous ,
To my knowledge, It's suggested to add a new table as you used before. You could use ALLSELECTED() to select necessary columns as a calculated table instead of duplicating the whole table.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello! thanks for your help. How would I use allselected () ? I've been trying to figure it out, however im prettu new to dax therefore i am having some troubles. Here is what I have done:
I have created a new column that is a copy of the one used in the slicer (OL =MP[descrição])
Changed the structre to
And the measure to :
my logic here (which is probably wrong) is with ALLSELECTED() i have created a new table with my key and the column i want to filter out, that table is then filteres using FILTER() using the SELECTEDVALUE() of the slicer selection. then changed my sclier to use MP[descricao] as its field.
but if the silcers selections are different from each other i get blanks, otherwise I get an error stating that PBI could not convert the selected value (text) into a true/false argument.
Hi Anonymous ,
The method you used before is correct. Actually I mean you could create a calculated table via ALLSELECTED() not getting the selected value in slicer like this:
New Table=DISTINCT(ALLSELECTED('MP'[Desc],MP[VALOR]))Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.