Forum Discussion

XeniaLi's avatar
XeniaLi
Helper I
1 year ago
Solved

FILTER context keep in one variable and remove in other.

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's avatar
    XeniaLi
    1 year ago

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

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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's avatar
      XeniaLi
      Helper I
      1 year ago

      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

      IdTeamCodeEventTeamUserId
      1TeamASCF1
      2TeamARET2
      3TeamAINP3
      4TeamCSpecial Team1
      5TeamCSCF014
      6TeamCINP5
      7TeamBINP1
      8TeamBRET2
      9TeamBRET3
      10TeamBRET4
      11TeamBCAS5

       

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

      IdTeamCodeEventTeamUserIdCount
      7TeamBINP11
      10TeamBRET41

       

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Super User
    1 year ago

    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
)
  • XeniaLi's avatar
    XeniaLi
    Helper I
    1 year ago

    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's avatar
      v-hjannapu
      Community Support
      1 year ago

      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.