Dynamic filter on multiple columns

[isThatAFrog]

Hi Community,

I have a DAX filter that is not as dynamic as I would like, but i dont know how to change it can you help?
I need to know if a move goes from a location that isnt the selected location to the selected location


Using this formula it gave me the result that I needed, but only for one location in one column

Entries = CALCULATE([CountMoves], CONTAINSSTRING( 'Mobility All'[TO loc], "Danmark") &&NOT(CONTAINSSTRING( 'Mobility All'[FROM loc], "Danmark") )

I tried this but it didnt seem to do what I wanted when adding filters multipe columns, or when reversing the formula for finding exits

Entries = CALCULATE([CountMoves], NOT(SELECTEDVALUE('Mobility All'[FROM Loc])) IN VALUES('Mobility All'[TO Loc]) ) ​

I'm attempting to create a bar chart with the + and - moves for the selected TO locations and possibly an aggregated chart as well, which does work for the not dynamic formula

Example of data:

Person ID	Date	From loc	From dep	From type	To loc	To dep	To type
1	01-01-22	Denmark	HR	intern	Denmark	FIN	Student
2	01-01-22	Norway	FIN	employee	Denmark	CCS	employee
1	10-12-22	Denmark	FIN	Student	Denmark	FIN	employee
3	31-10-23	Denmark	CCS	external	Germany	CCS	employee
3	02-05-24	Germany	CCS	employee	Germany	FIN	employee

[Anonymous · Accepted solution]

Hi All,

Firstly,  @amitchandak thanks for the solution!

And @isThatAFrog  according to my understanding,  I want to share another method. 

I think your need is to count the number of people arriving at different places and leaving to arrive at other places.

Entries = 
CALCULATE(
    [CountMoves],
    FILTER(
        'Mobility All',
        'Mobility All'[TO loc] = SELECTEDVALUE('Mobility All'[TO loc]) &&
        'Mobility All'[FROM loc] <> SELECTEDVALUE('Mobility All'[TO loc])
    )
)

Exits = 
CALCULATE(
    [CountMoves],
    FILTER(
        'Mobility All',
        'Mobility All'[FROM loc] = SELECTEDVALUE('Mobility All'[FROM loc]) &&
        'Mobility All'[TO loc] <> SELECTEDVALUE('Mobility All'[FROM loc])
    )
)

As per your example data, there are two ids for arriving elsewhere and two for leaving the region, which can be reflected in the table, I tried to use a bar chart for your requirement to create, using To loc as the x-axis and adding From Ioc to the filter can help you get the effect you want.

If you have any other questions, you can check out the attachments I've provided and I hope they will help you solve the problem.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@isThatAFrog , Try like

Entries = CALCULATE([CountMoves], except(All('Mobility All'[TO Loc]),SELECTEDVALUE('Mobility All'[FROM Loc]) )) ​

or

Entries = CALCULATE([CountMoves], filter(all('Mobility All'[FROM Loc]) , 'Mobility All'[FROM Loc] in except(All('Mobility All'[TO Loc]),SELECTEDVALUE('Mobility All'[FROM Loc]) )) ​)

you might need to consider if you are looking for exclude scenario

Need of an Independent/disconnected Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE