Forum Discussion

timward10's avatar
timward10
Helper II
1 year ago
Solved

Filter help

Hi, 

 

I have a report with multiple tables on it, everything is working currently until I select the below field as a filter and it just doesn't populate with any account names. 

 

I have added the formula I am using to return the account name from another table, but can't work out what it is I am doing wrong. 

 

 

Can anyone help or point me in the right direction please? 

 

Thanks!

 

Need Help
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

    Hi timward10 

    Try this one:

    Account Name from Demand Gen =
VAR UltimateParentCode = SELECTEDVALUE(event[Ultimate Parent IFS Code])
VAR UltimateParentCodeAsText = CONVERT(UltimateParentCode, STRING)  // Convert integer to text
VAR MatchedAccount = 
    LOOKUPVALUE(
        'Demand Gen Target List'['Demand Gen Account Name'],
        'Demand Gen Target List'[InvoiceCustomerNumber], UltimateParentCodeAsText,  // Now comparing text-to-text
        BLANK()
    )
RETURN
    IF(ISBLANK(MatchedAccount), "Not Found", MatchedAccount)

     

    If this post helps, then please  don't forget to give a "Kudos" – I’d truly appreciate it! 

    Thank you.

6 Replies

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    Hi timward10 ,

     

    If your filter isn’t showing any account names, here are a few things to check:

    1. Make sure there’s a relationship between AccountsTable and AnotherTable on AccountID.
    2. Double-check that the Condition = 'Value' part actually returns matching AccountIDs.
    3. Ensure both AccountID columns are of the same data type (e.g., both text or both number).
    4. If you're doing this in Power BI, you might want to use a DAX expression like:
    FilteredAccounts = 
CALCULATETABLE(
    VALUES(AccountsTable[AccountName]),
    FILTER(
        AnotherTable,
        AnotherTable[Condition] = "Value"
    )
)

     

    If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
    translation and formatting supported by AI

  • Elena_Kalina's avatar
    Elena_Kalina
    Solution Sage
    1 year ago

    Hi timward10 

    Try this one: 

    Account Name from Demand Gen =
VAR UltimateParentCode = SELECTEDVALUE(event[Ultimate Parent IFS Code])
VAR MatchedAccount = 
    LOOKUPVALUE(
        'Demand Gen Target List'['Demand Gen Account Name],
        'Demand Gen Target List'[InvoiceCustomerNumber], UltimateParentCode,
        BLANK()
    )
RETURN
    IF(ISBLANK(MatchedAccount), "Not Found", MatchedAccount)

     

    • timward10's avatar
      timward10
      Helper II
      1 year ago

      Thanks, I am getting this error message now around formatting. 

       

       

      • timward10's avatar
        timward10
        Helper II
        1 year ago

        Elena_Kalina I have fixed the format issue, but it is not returning any account names, just 'Not matched' the whole way down? Thanks

  • v-kathullac's avatar
    v-kathullac
    Community Support
    1 year ago

    Hi timward10 ,

    Thank you for reaching out to Microsoft Fabric Community Forum.

    can you try the below dax measure

     

    Account Name from Demand Gen =
    VAR UltimateParentCode = VALUE(SELECTEDVALUE(event[Ultimate Parent IDS Code]))
    VAR MatchCheckCount =
    LOOKUPVALUE(
    'Demand Gen Target List'[Demand Gen Account Name],
    'Demand Gen Target List'[InvoiceCustomerNumberPM], UltimateParentCode,
    BLANK()
    )
    RETURN
    IF(ISBLANK(MatchCheckCount), "Not Found", MatchCheckCount)

     

    Regards,

    Chaithanya.