Filter based on a condition

[Isoka]

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

[amitchandak]

@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

https://docs.microsoft.com/en-us/dax/treatas-function

[Isoka]

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,

[Isoka · Accepted solution]

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

[Anonymous]

Hi @Isoka ,

Thank you for sharing, is there anything else you need help with regarding this thread? If not, would it be possible to mark your reply as Answered? This will also help the others in the community who have similar problems to yours to find a solution as soon as possible. Thank you.

By the way, about Table _SegGeral2, maybe you can select all in_out columns and unpivot them in Query Editor first as in the figure below, so that it may be more convenient to calculate later.

Best Regards