FILTER context keep in one variable and remove in other.

[XeniaLi]

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

[XeniaLi · Accepted solution]

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])
)

[v-hjannapu]

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.

[MohamedFowzan1]

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
)

[Greg_Deckler]

@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.

[XeniaLi]

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