cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Partisan

## Requesting help for customer requirement

Hello everybody,

I've been spending a while thinking on how to solve a customer question. Can't get it to work like the way the customer wants to see it (check the desired result at the bottom). I have provided example data. Hopefully someone can help me.

I have a list of available codes per sector (template). I also have a list of companies with a sector and a code (fact table). A code can occur multiple times for a company. The customer wants to see an overview of all companies and which codes from the template have been used (including those who have not been used). There is an example below. Let me know if there further explanation needed. Thank you very much!

Example data

Template Codes (dimension)

 Sector Code 1 1000 1 2000 1 3000 1 4000 1.1 11 1.1 12 2 250 2 275

Company Codes (fact)

 Company Sector Code Microsoft 1 2000 Microsoft 1 3000 Microsoft 1 3000 Microsoft 1 3000 Microsoft 1 4000 Microsoft 1 4000 Microsoft 1 4000 Apple 1.1 11 Apple 1.1 11 Google 2 9999

Desired result:

 Company Sector Template codes TemplateCode_IsNotInCompanyCodes(0),TemplateCode_IsInCompanyCodes (1),TemplateCode_IsDoubleOrMoreInCompanyCodes (2 or more) Microsoft 1 1000 0 Microsoft 1 2000 1 Microsoft 1 3000 3 Microsoft 1 4000 3 Apple 1.1 11 2 Apple 1.1 12 0 Google 2 250 0 Google 2 275 0

1 ACCEPTED SOLUTION
Community Support

HI @DeBIe,

You can create a calculated column on the template table to lookup values from the company table, then you can write a measure to calculate the count.

Calculate column:

``Company = LOOKUPVALUE('Company Code'[Company],'Company Code'[Sector],'Template Codes'[Sector])``

Measure formula:

``````formula =
CALCULATE (
COUNTA ( 'Company Code'[Code] ) + 0,
FILTER (
ALLSELECTED ( 'Company Code' ),
'Company Code'[Code]
IN VALUES ( 'Template Codes'[Code] )
&& 'Company Code'[Sector] IN VALUES ( 'Template Codes'[Sector] )
)
)``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
4 REPLIES 4
Community Support

HI @DeBIe,

You can create a calculated column on the template table to lookup values from the company table, then you can write a measure to calculate the count.

Calculate column:

``Company = LOOKUPVALUE('Company Code'[Company],'Company Code'[Sector],'Template Codes'[Sector])``

Measure formula:

``````formula =
CALCULATE (
COUNTA ( 'Company Code'[Code] ) + 0,
FILTER (
ALLSELECTED ( 'Company Code' ),
'Company Code'[Code]
IN VALUES ( 'Template Codes'[Code] )
&& 'Company Code'[Sector] IN VALUES ( 'Template Codes'[Sector] )
)
)``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Post Partisan

Hey @v-shex-msft ,

Thank you very much for helping me.

I provided sample data in my post and I tested your logic with that sample data and it works. But for my real dataset, I am getting this error when creating the lookup column. I don't understand why it works for sample data and not for my real dataset.

Post Partisan

For some reason I had to use this alternative for the lookup to get it working:
Solved: LOOKUPVALUE - "A table of multiple values was supp... - Microsoft Power BI Community

Post Partisan

@v-shex-msft

At first sight I thought it was working, but using the alternative for lookup gives me invalid results. Any ideas why the normal Lookup is not working? I made sure I got no blanks in the query editor. In the report view I am also not seeing any duplicates.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors