Forum Discussion
TREATAS filter propagation
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?
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.
5 Replies
smpa01Community Champion
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.
- smpa01
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?
v-yalanwu-msftCommunity Support
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.- v-yalanwu-msft
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.
Best Regards,Community Support Team_ Yalan WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.