Forum Discussion

Yiyi's avatar
Yiyi
Icon for Helper I rankHelper I
3 years ago
Solved

A table created by table function can not be used in VALUE() function?

Hello, this is my code and to me, it looks all fine, but the error message said that Cannot find table 'AISearchUsers' when I used it with VALUE() after RETURN hoping to use it as a list to filter my table. Can you tell me why? The options to VALUE() seems to be all original tables but not the one I just created with the VAR. 

 

Count_users_who_only_use_keyword_search =
VAR AISearchUsers =
    SUMMARIZE(
        FILTER('Query 3', 'Query 3'[IsAISearch] = "true"),
        'Query 3'[user_Id]
    )
RETURN
CALCULATE(
    DISTINCTCOUNT('Query 3'[user_Id]),
    FILTER(
        AISearchUsers,
        NOT('Query 3'[user_Id] IN VALUES(AISearchUsers[user_id]))
    ),
    'Query 3'[IsAISearch] = "false"
)

Thanks!  

"DAX"
DAX SUMMARIZE
  • nandukrishnavs's avatar
    nandukrishnavs
    3 years ago

    Yiyi 

     

    try this 

    Make yourCount_users_who_only_use_keyword_search =
VAR AISearchUsers =
    SUMMARIZE (
        FILTER (
            'Query 3',
            'Query 3'[IsAISearch] = "true"
        ),
        'Query 3'[user_Id]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Query 3'[user_Id] ),
        FILTER (
            'Query 3',
            NOT ( [user_Id] IN AISearchUsers )
        ),
        'Query 3'[IsAISearch] = "false"
    )

4 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Yiyi  Try this 

     

     

     

     

    Make yourCount_users_who_only_use_keyword_search =
VAR AISearchUsers =
    SUMMARIZE (
        FILTER (
            'Query 3',
            'Query 3'[IsAISearch] = "true"
        ),
        'Query 3'[user_Id]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Query 3'[user_Id] ),
        FILTER (
            AISearchUsers,
            NOT (
                VALUES ( 'Query 3'[user_Id] )
                    IN AISearchUsers
            )
        ),
        'Query 3'[IsAISearch] = "false"
    )

     

     

    • Yiyi's avatar
      Yiyi
      Icon for Helper I rankHelper I
      3 years ago

      Thanks for your reply! I really appreciate it! Now this code seems ok, but I can not add it to an visualization and it throws out such error message that: 

       

      I tried to use the VAR to create a list of users who have used the AI search function and then in the RETURN I tried to get a list of users who have not used yet the AI search function but who has used the Keyword function. Many users who have used both search functions. 

       

      Can you give me some idea about where the issue of the code can be? Thanks a lot!

       

      • nandukrishnavs's avatar
        nandukrishnavs
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Yiyi 

         

        try this 

        Make yourCount_users_who_only_use_keyword_search =
VAR AISearchUsers =
    SUMMARIZE (
        FILTER (
            'Query 3',
            'Query 3'[IsAISearch] = "true"
        ),
        'Query 3'[user_Id]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Query 3'[user_Id] ),
        FILTER (
            'Query 3',
            NOT ( [user_Id] IN AISearchUsers )
        ),
        'Query 3'[IsAISearch] = "false"
    )