In my company I'm trying to create a Power BI report that relies on a company provided semantic model and my own Excel table. The provided semantic model is only available as directquery at the moment. My data looks like the following:
Company Data - DIM_customer (DQ)
| customer key | customer name | Special customer |
|---|---|---|
| '000025468' | 'Company 1' | 'yes' |
| '000025692' | 'Company 2' | 'no' |
| '000034468' | 'Company 3' | 'no' |
| '000051692' | 'Company 4' | 'yes' |
| ........... | ........... | ..... |
Excel Data - FACT_sale (import)
| Invoice | Sale Amount | customer key |
|---|---|---|
| '1000' | 100 | '000025468' |
| '1001' | 200 | '000025468' |
| '1002' | 100 | '000034468' |
| '1003' | 400 | '000051692' |
| ...... | ... | ........... |
The Relationship is created on the customer key and is a One-to-many (DIM -> FACT). The relationship is limited.
The measure i would like to create is a measure that calculates the total sale of special customer.
What i want to do:
SUM(FACT_sale[Sale Amount]) WHERE DIM_customer[Special customer] = 'yes'
I've tried the following DAX measure:
CALCULATE(SUM(FACT_sale[Sale Amount]), RELATED(DIM_customer[Special customer]) = 'yes')
But as i understand it, since the relationship is limited, this measure won't work as it can't use the RELATED function on the two tables..
What would my options be, to make this measure (or similar) work? - is there a work-around for this limited relationship? Would i have to ask the semantic model administrator for a different data setup? (low chance of success)
Thanks in advance!