Forum Discussion

Isoka's avatar
Isoka
Frequent Visitor
4 years ago
Solved

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...
Need Help
  • Isoka's avatar
    Isoka
    4 years ago

    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

Isoka's avatar
Isoka
Frequent Visitor
4 years ago

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:

CountryIn_Out1In_Out2In_Out3In_Out4
Brazil    
Germany1023935
USA6062  
Colombia    
Denmark324650 

 

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:
ValueIn_OutCond2Cond3
3010xg
6580yd
79663sr
3446dt
21450qt
375103et
2910fy
78550sg
99292sn

 

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's avatar
Isoka
Frequent Visitor
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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