Forum Discussion
Filter based on a condition
Dear all,
I can't figure out how to apply a filter based on a condition. For instance, I would like to filter column "Area" from table "A" based on column "Area" from table "B". But I want to use this column in a SELECTEDVALUE statement so that if column "Area" from table "B" is not blank, then column "Area" from table "A" will be filtered. The idea is that if there is a blank cell there will be no filter for that specific column. I have put it here in a simple way but there will be a big table with many columns from where the SELECTEDVALUE will come from and I need to make this DAX as generic as possible.
I tried and IF statement inside a measure with the filter being one of the results but it is not allowed.
Any help will be appreciated 🙂
Best regards,
Isoka
Table A:
| Seller | ID | Amount | Area |
| John | 1 | 100 | A |
| Mark | 2 | 200 | B |
| Paul | 3 | 300 | C |
Table B:
| Selectedvalue | Area |
| 1 | A |
| 2 | |
| 3 | B |
I have just found out that writing the code with the whole CALCULATE inside de IF statement it works. Like this:
mymeasure = IF (1=1,
CALCULATE(
SUM(),
FILTER()
),
result if false
)
But if someone has a more elegant way to accomplish what I have explained in the first post I would really like to learn.
Best regards,
Isoka
4 Replies
Isoka , Not very clear, share the expected output with example
you can create a measure like this for table B
countrows(filter(TableB, TableB[Selectedvalue] in allselected(TableA[Area]) ) )
also check
Dear amitchandak , thank you for your interest!
Let me bring this closer to the reality, I apologize for being to generic.
I used a table like the one below and put column "Country" in a slicer.
Table _SegGeral2:
Country In_Out1 In_Out2 In_Out3 In_Out4 Brazil Germany 10 23 9 35 USA 60 62 Colombia Denmark 32 46 50 Then I created the following measure to sum values on table "QUOTE_REPORT" :
Sum(QUOTE_REPORT[Value]IF(SELECTEDVALUE(_SegGeral2[In_Out1])<>"",Filter(QUOTE_REPORT,[In_Out] IN {SELECTEDVALUE(_SegGeral2[In_Out1]),SELECTEDVALUE(_SegGeral2[In_Out2]),SELECTEDVALUE (_SegGeral2[In_Out3]),SELECTEDVALUE(_SegGeral2[In_Out4])})))Table QUOTE_REPORT:Value In_Out Cond2 Cond3 30 10 x g 65 80 y d 796 63 s r 34 46 d t 214 50 q t 375 103 e t 29 10 f y 785 50 s g 992 92 s n In the fact table there will always be a value in column "In_Out" but in table "_SegGeral2" column "In_Out1" there may be blank values.
The expected result is that when I select Brazil in the slicer, since there is no value in column "In_Out1", the filter will not be included in the SUM statement. But when I select Germany it must be included.
I will have the same situation for "Cond2", "Cond3" and a few other conditions. That is why I am trying to find a way to include a filter, inside a SUM statement, conditioned to the existence of values in certain columns on the dimension table.
Hope I was able to make it clearer.
Best regards,
I have just found out that writing the code with the whole CALCULATE inside de IF statement it works. Like this:
mymeasure = IF (1=1,
CALCULATE(
SUM(),
FILTER()
),
result if false
)
But if someone has a more elegant way to accomplish what I have explained in the first post I would really like to learn.
Best regards,
Isoka
