Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
2 | 9999 |
Desired result:
Company | Sector | Template codes | TemplateCode_IsNotInCompanyCodes(0), TemplateCode_IsInCompanyCodes (1), |
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 |
2 | 250 | 0 | |
2 | 275 | 0 |
Solved! Go to Solution.
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
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
Hey @Anonymous ,
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.
@Anonymous
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
Thanks for your help!
@Anonymous
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
55 | |
54 | |
37 | |
29 |
User | Count |
---|---|
77 | |
62 | |
45 | |
40 | |
40 |