Forum Discussion

DeBIe's avatar
DeBIe
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

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). ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

DeBIe's avatar
DeBIe
Icon for Post Partisan rankPost Partisan
4 years ago

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.

 

 

 

 



  • DeBIe's avatar
    DeBIe
    Icon for Post Partisan rankPost Partisan
    4 years ago

    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.