Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
Given the following data model:
I would like to create a measure that would aggregate an attribute from the table [ServiceCases]. The design should allow me also to slice [ServiceCases] table using [SalesInvoiceDate] time table.
This is the initial code I created:
Some fee =
CALCULATE(
[# ServiceCases], -- COUNTROWS(ServiceCases)
USERELATIONSHIP(SalesInvoiceDate[SalesInvoiceDate],SalesOrder[InvoiceDate]), -- to activate the relationship
TREATAS(values(ServiceCases[RelatedDocumentNo]),SalesOrder[InternDocumentNumber]) -- to map one table into another)
)
Unfortunately, when I put attributes and the measure I'm interested in, I get record multuplication:
Can anyone explain to me this strange behaviour?
Solved! Go to Solution.
@Anonymous I tested out with the following model and it is giving me what was desired
| SalesInvoice |
|------------------|
| SalesInvoiceDate |
|------------------|
| 2021-01-01 |
| 2021-01-02 |
| 2021-01-03 |
| SalesOrder |
|-------------|------|------------|
| InvoiceDate | Doc# | Date |
|-------------|------|------------|
| 2021-01-01 | 123 | 2021-01-01 |
| 2021-01-02 | 231 | 2021-01-02 |
| 2021-01-03 | 312 | 2021-01-03 |
| 2021-01-01 | 456 | 2021-01-01 |
| 2021-01-02 | 567 | 2021-01-02 |
| 2021-01-03 | 789 | 2021-01-03 |
| ServiceCases |
|--------------|
| Docu# |
|--------------|
| 456 |
| 456 |
| 789 |
| 456 |
| 789 |
| 1000 |
| 2000 |
pbix is attached
If you are experiencing a different behaviour, can you please post the pbix here?
Hi, @Anonymous ;
I tested by simple data ,and you could try it .
Some fee =
VAR _id =
SUMMARIZE (
FILTER (
ALL ( 'SalesOrder' ),
'SalesOrder'[InvoiceDate] = MAX ( 'SalesOrder'[InvoiceDate] )
),
[InternDocumentNumber]
)
RETURN
CALCULATE (
COUNTROWS (
FILTER ( ALL ( 'ServiceCases' ), 'ServiceCases'[RelatedDocumentNo] IN _id )
),
USERELATIONSHIP ( 'SalesInvoice'[SalesInvoiceDate], 'SalesOrder'[InvoiceDate] )
)
The final output is shown below:
If not right, can you tell the result what you want output in my simple data or change the simple data to similar with yours?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
I tested by simple data ,and you could try it .
Some fee =
VAR _id =
SUMMARIZE (
FILTER (
ALL ( 'SalesOrder' ),
'SalesOrder'[InvoiceDate] = MAX ( 'SalesOrder'[InvoiceDate] )
),
[InternDocumentNumber]
)
RETURN
CALCULATE (
COUNTROWS (
FILTER ( ALL ( 'ServiceCases' ), 'ServiceCases'[RelatedDocumentNo] IN _id )
),
USERELATIONSHIP ( 'SalesInvoice'[SalesInvoiceDate], 'SalesOrder'[InvoiceDate] )
)
The final output is shown below:
If not right, can you tell the result what you want output in my simple data or change the simple data to similar with yours?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous I tested out with the following model and it is giving me what was desired
| SalesInvoice |
|------------------|
| SalesInvoiceDate |
|------------------|
| 2021-01-01 |
| 2021-01-02 |
| 2021-01-03 |
| SalesOrder |
|-------------|------|------------|
| InvoiceDate | Doc# | Date |
|-------------|------|------------|
| 2021-01-01 | 123 | 2021-01-01 |
| 2021-01-02 | 231 | 2021-01-02 |
| 2021-01-03 | 312 | 2021-01-03 |
| 2021-01-01 | 456 | 2021-01-01 |
| 2021-01-02 | 567 | 2021-01-02 |
| 2021-01-03 | 789 | 2021-01-03 |
| ServiceCases |
|--------------|
| Docu# |
|--------------|
| 456 |
| 456 |
| 789 |
| 456 |
| 789 |
| 1000 |
| 2000 |
pbix is attached
If you are experiencing a different behaviour, can you please post the pbix here?
@Anonymous can you try this instead
Some fee =
CALCULATE(
[# ServiceCases], -- COUNTROWS(ServiceCases)
USERELATIONSHIP(SalesInvoiceDate[SalesInvoiceDate],SalesOrder[InvoiceDate]), -- to activate the relationship
TREATAS(values(SalesOrder[InternDocumentNumber]),ServiceCases[RelatedDocumentNo]) -- to map one table into another)
)
@smpa01 The value for metric [Some fee] actually changed, but it's still a total spread across all the lines, w/o the slice I'm looking for, so it doesn't solve the issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |