TREATAS filter propagation

[Anonymous]

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?

[smpa01 · Accepted 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?

[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 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 · Accepted solution]

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.

[smpa01 · Accepted 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?

[smpa01]

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

[Anonymous]

@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.