Paginated Report Query Help

[laurenm]

This is my first real paginated report using report builder, other than a couple of column drop tables in the service. I hate it. I understand that relationships across tables do not come into the builder when using the semantic model connection but do not understand how to work around that with the DAX. 
My visual would be a matrix with the following - 
Category - Table A
SubCategory - Table B

FiscalYear - Table C
And then this measure which sums a column from Table D summarized by those - 

2YTDNetDollar =
var curryr = YEAR(TODAY())
var closmo = MONTH(TODAY())-1
RETURN
CALCULATE(SUM('Net Sales'[LineTotal]),FILTER(ALL('Net Sales'[DocDate]),YEAR('Net Sales'[DocDate])>=curryr-1 && YEAR('Net Sales'[DocDate])<=curryr && MONTH('Net Sales'[DocDate])<=closmo))


Table D is connected to Table A by a product code column, Table B by a matching subcategory column, and Table C (dates table) by the DocDate. So how do I get the right total from my measure in the paginated report? 

Thank you!

[Ahmed-Elfeel · Accepted solution]

Hi @laurenm,

I hope you are doing well today ☺️❤️

The issue here is that DAX measures need explicit context propagation since the matrix visual doesn't automatically bridge relationships like Power BI does

So You have 2 Options and 1 Option If relationships exist in the model...Let's head over:

First Option is to use RELATEDTABLE or FILTER with relationships :

2YTDNetDollar =
VAR curryr = YEAR(TODAY())
VAR closmo = MONTH(TODAY()) - 1
RETURN
CALCULATE(
    SUM('Net Sales'[LineTotal]),
    FILTER(
        ALL('Date'[Date]),  // Use your date table here
        YEAR('Date'[Date]) >= curryr - 1 && 
        YEAR('Date'[Date]) <= curryr && 
        MONTH('Date'[Date]) <= closmo
    ),
    // explicitly bridge the relationships
    FILTER(
        'Net Sales',
        'Net Sales'[ProductCode] IN VALUES('Table A'[ProductCode]) &&
        'Net Sales'[SubCategory] IN VALUES('Table B'[SubCategory])
    )
)

Second Option is to use TREATAS to establish virtual Relationships:

2YTDNetDollar =
VAR curryr = YEAR(TODAY())
VAR closmo = MONTH(TODAY()) - 1
RETURN
CALCULATE(
    SUM('Net Sales'[LineTotal]),
    FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) >= curryr - 1 && 
        YEAR('Date'[Date]) <= curryr && 
        MONTH('Date'[Date]) <= closmo
    ),
    TREATAS(VALUES('Table A'[ProductCode]), 'Net Sales'[ProductCode]),
    TREATAS(VALUES('Table B'[SubCategory]), 'Net Sales'[SubCategory])
)

The Third Option is for relationships exist in your model (CROSSFILTERS):

2YTDNetDollar =
VAR curryr = YEAR(TODAY())
VAR closmo = MONTH(TODAY()) - 1
RETURN
CALCULATE(
    SUM('Net Sales'[LineTotal]),
    FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) >= curryr - 1 && 
        YEAR('Date'[Date]) <= curryr && 
        MONTH('Date'[Date]) <= closmo
    ),
    CROSSFILTER('Net Sales'[ProductCode], 'Table A'[ProductCode], BOTH),
    CROSSFILTER('Net Sales'[SubCategory], 'Table B'[SubCategory], BOTH)
)

• I would recommend Option 2 Cause it is best for most paginated report scenarios and better performance and star schemas.

Let me Know if this works ☺️❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[laurenm]

Perfect! Thank you for the thorough and helpful solution. This is great. One day maybe I'll understand these things but it working is the next best thing to knowledge 😉 

[Ahmed-Elfeel]

Good Luck My Friend ☺️❤️