Issue with SELECTEDVALUE function when used with CALCULATETABLE in DAX

[Yiyi_1989]

Hello,

I am currently working on a report where I've dedicated a page to individual companies. Users navigate to this page via "drill through" from another page. On this particular page, I would like to see other companies that are within the same industry as the company in question. With that aim, I have constructed the following DAX formula:

SummarizeTable = 

VAR CurrentIndustry = SELECTEDVALUE('Account Meta Data'[Industry])

VAR CompanyList = CALCULATETABLE( 
    VALUES('Account Meta Data'[DatabaseName]), 
    'Account Meta Data'[Industry] = CurrentIndustry
)

Return
CompanyList

However, the CompanyList produced doesn't align with my expectation. It seems to comprise of companies which lack industry definitions in my Company Meta Table. Although I have tested the CurrentIndustry = SELECTEDVALUE('Account Meta Data'[Industry]) measure separately and confirmed it produces the correct value, it doesn't seem to function as intended within the CALCULATETABLE function. When I replace CurrentIndustry with a specific industry value (for example, "Agriculture"), the list is generated correctly.

Could someone kindly help me understand what might be wrong? Any assistance would be greatly appreciated!

Best regards,

Brenda

[v-yifanw-msft]

Thank @_AAndrade   for your prompt reply.

Hi @Yiyi_1989 ,

Based on your description of the problem, the heart of the issue seems to be how the CALCULATETABLE function interprets the CurrentIndustry variable in its filtering context. Here is a modification to your DAX formula that should solve the problem:

SummarizeTable =
VAR CurrentIndustry =
    SELECTEDVALUE ( 'Account Meta Data'[Industry] )
VAR CompanyList =
    CALCULATETABLE (
        VALUES ( 'Account Meta Data'[DatabaseName] ),
        FILTER ( 'Account Meta Data', 'Account Meta Data'[Industry] = CurrentIndustry )
    )
RETURN
    CompanyList

I encapsulated the filter condition with the FILTER function. This explicitly creates row context for each row in the Company Meta table so that comparisons between each company's industry and the CurrentIndustry variable can be evaluated correctly.

CALCULATETABLE Functions:Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn
Try this modified formula to see if it works as expected.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yiyi_1989]

Hello @_AAndrade 

Thanks for promptly responding to my query.

To add some clarity, I would like to generate a list of companies that are in the same industry as the industry selected on a particular page. This data is to be extracted from the 'Account Meta Data' table.The 'Account Meta Data' table consists of several details such as the Company name, Database Name, Industry, and more. My goal is to filter this table based on industry and create a list showcasing the names of the companies within that industry.

To illustrate, if a specific company page filters for 'Software' industry, I'd aim to generate a 'Software' industry-specific list of companies from the 'Account Meta Data' table.

Due to the confidentiality of my data, I’m unable to share explicit data. I would really appreciate if you have some general suggestion about what I should check. Thanks a lot!!

[_AAndrade]

Hi @Yiyi_1989,

It seems your formula don't produce a scalar value but a table with more than one value. I don't Know if this is the problem.
Can you share one pbix file with same data so I can take a look?