cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamilzet__
Frequent Visitor

TREATAS filter propagation

Hello

Given the following data model:

kamilzet___1-1636987975537.png

 


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:

kamilzet___2-1636988400224.png

Can anyone explain to me this strange behaviour?



2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

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

 

smpa01_0-1637091478270.png

 

 

pbix is attached

smpa01_1-1637091572411.png

 

If you are experiencing a different behaviour, can you please post the pbix here?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

v-yalanwu-msft
Community Support
Community Support

Hi, @kamilzet__ ;

I tested by simple data ,and you could try it .

vyalanwumsft_0-1637210878019.png

 

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:

vyalanwumsft_1-1637210965308.png

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.

 

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @kamilzet__ ;

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
Community Support
Community Support

Hi, @kamilzet__ ;

I tested by simple data ,and you could try it .

vyalanwumsft_0-1637210878019.png

 

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:

vyalanwumsft_1-1637210965308.png

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
Super User
Super User

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

 

smpa01_0-1637091478270.png

 

 

pbix is attached

smpa01_1-1637091572411.png

 

If you are experiencing a different behaviour, can you please post the pbix here?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


smpa01
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors