topic TREATAS filter propagation in DAX Commands and Tips

TREATAS filter propagation

Anonymous — Mon, 15 Nov 2021 15:02:42 GMT

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?

Re: TREATAS filter propagation

smpa01 — Mon, 15 Nov 2021 16:57:30 GMT

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)
)

Re: TREATAS filter propagation

Anonymous — Tue, 16 Nov 2021 06:50:11 GMT

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.

Re: TREATAS filter propagation

smpa01 — Tue, 16 Nov 2021 19:40:29 GMT

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?

Re: TREATAS filter propagation

v-yalanwu-msft — Thu, 18 Nov 2021 04:51:47 GMT

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.

Re: TREATAS filter propagation

v-yalanwu-msft — Wed, 24 Nov 2021 05:47:30 GMT

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 Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.