cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeBIe
Post Patron
Post Patron

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)

SectorCode
11000
12000
13000
14000
1.111
1.112
2250
2275

 

 

Company Codes (fact)

CompanySectorCode
Microsoft12000
Microsoft13000
Microsoft13000
Microsoft13000
Microsoft14000
Microsoft14000
Microsoft14000
Apple1.111
Apple1.111
Google29999

 

Desired result:

CompanySectorTemplate codes

TemplateCode_IsNotInCompanyCodes(0),

TemplateCode_IsInCompanyCodes (1),
TemplateCode_IsDoubleOrMoreInCompanyCodes (2 or more)

Microsoft110000
Microsoft120001
Microsoft130003
Microsoft140003
Apple1.1112
Apple1.1120
Google22500
Google22750

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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] )
    )
)

 

1.png

Regards,

Xiaoxin Sheng

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

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
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] )
    )
)

 

1.png

Regards,

Xiaoxin Sheng

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

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.

 

DeBIe_0-1653653294042.png

 

 

 



@v-shex-msft 

 

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!

@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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors