topic Re: FILTER context keep in one variable and remove in other. in DAX Commands and Tips

FILTER context keep in one variable and remove in other.

XeniaLi — Mon, 04 Aug 2025 15:45:16 GMT

Hi I'm stuck with DAX.

I need to find Users that exists in both variabales, I need to keep filter for 1 variable when selecting Users ID, but in return part I need to show Event'[Team] for TeamB. Keep the parameter of UserId but ignore EventID from Variable 1.

VAR OtherTeamSelect =

SELECTCOLUMNS(

FILTER('FactTable',

'FactTable'[MeasureKey] = 81 &&

RELATED('Team'[Team Code]) IN {"TeamA","TeamC"} &&

CONTAINSSTRING( RELATED('Event'[Team]),"SCF")|| CONTAINSSTRING( RELATED('Event'[Team]),"Speciali Team") || CONTAINSSTRING( RELATED('Event'[Team]),"SCF01")),

"User", 'FactTable'[User])

RETURN

CALCULATE(COUNTROWS('FactTable'),

'FactTable'[MeasureKey] = 1,

'Team'[Team Code] = "TeamB",

TREATAS(OtherTeamSelect, 'FactTable'[User])

)

TIA

Re: FILTER context keep in one variable and remove in other.

Greg_Deckler — Mon, 04 Aug 2025 15:57:57 GMT

XeniaLi Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: FILTER context keep in one variable and remove in other.

MohamedFowzan1 — Mon, 04 Aug 2025 17:21:23 GMT

Please provide more context if this is not what you are looking for:

VAR OtherTeamSelect = SELECTCOLUMNS( FILTER( 'FactTable', 'FactTable'[MeasureKey] = 81 && RELATED('Team'[Team Code]) IN {"TeamA", "TeamC"} && ( CONTAINSSTRING(RELATED('Event'[Team]), "SCF") || CONTAINSSTRING(RELATED('Event'[Team]), "Speciali Team") || CONTAINSSTRING(RELATED('Event'[Team]), "SCF01") ) ), "User", 'FactTable'[User] ) RETURN CALCULATE( COUNTROWS('FactTable'), 'FactTable'[MeasureKey] = 1, 'Team'[Team Code] = "TeamB", TREATAS(OtherTeamSelect, 'FactTable'[User]), ALL('FactTable'[EventID]) -- same code and trying to ignore the filters on Event ID )

Re: FILTER context keep in one variable and remove in other.

XeniaLi — Mon, 04 Aug 2025 17:59:45 GMT

Hi all,
apologise I didn't explain it clear. I had to mention that I can't use M_Code and table transform as it is direct query of SSAS model.

So I need a measure that do something like this:

Original Table

Id	TeamCode	EventTeam	UserId
1	TeamA	SCF	1
2	TeamA	RET	2
3	TeamA	INP	3
4	TeamC	Special Team	1
5	TeamC	SCF01	4
6	TeamC	INP	5
7	TeamB	INP	1
8	TeamB	RET	2
9	TeamB	RET	3
10	TeamB	RET	4
11	TeamB	CAS	5

Result, I can't find the work around to keep EventTeam for TeamB from original table.

Id	TeamCode	EventTeam	UserId	Count
7	TeamB	INP	1	1
10	TeamB	RET	4	1

Re: FILTER context keep in one variable and remove in other.

XeniaLi — Tue, 05 Aug 2025 07:44:29 GMT

Morning all,
thanks all for looking, much appreciated, I've solve it. Wrapped selectcolumns in CALCULATETABLE and removed filter in 1st variable.

VAR OtherTeamSelect =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER('FactTable',
'FactTable'[MeasureKey] = 81 &&
RELATED('Team'[Team Code]) IN {"TeamA","TeamC"} &&
(CONTAINSSTRING( RELATED('Event'[Team]),"SCF")|| CONTAINSSTRING( RELATED('Event'[Team]),"Speciali Team") || CONTAINSSTRING( RELATED('Event'[Team]),"SCF01"))),
"User", 'FactTable'[User])),
ALL(Event))

RETURN
CALCULATE(COUNTROWS('FactTable'),
'FactTable'[MeasureKey] = 1,
'Team'[Team Code] = "TeamB",
TREATAS(OtherTeamSelect, 'FactTable'[User])
)

Re: FILTER context keep in one variable and remove in other.

Anonymous — Tue, 05 Aug 2025 10:01:53 GMT

Hi XeniaLi,

Thank you for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank MohamedFowzan1, Greg_Deckler for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

This information is very useful and will definitely assist others who encounter the same problem. and don’t hesitate to reach out to community support or post again if you have any questions or need support in the future.

Best regards,
Harshitha.