Latest Enterprise DNA Initiatives

Measure To Ignore Page Level Filter?

Hello,

I have a measure:

Sales Budget (Risk) Trend = calculate(sum(Backlog[Sales Budget]),all(Backlog[ReportRank]))

And I have a page-level filter Report Rank = 1. Report Rank is showing me which Report Date is the most recent one.

On the other hand, I have the area chart on which I want to use Report Date on X-axis and Sales Budget (Risk) Trend on Y-axis.

With this page-level filter Report Rank = 1, on the X-axis I see only the most recent date, whereas I should have seen more dates. Therefore, I wanted to use either all or removefilters , but none of them worked. They don’t change anything…

Does anyone know how to fix it?

Bottom: Current Status (Wrong)
Top: Desired Output

Correct
Wrong

Pbi_dset.pbix (85.9 KB)

Hello @marek.regulski,

Thank You for posting your query onto the Forum.

Is this the type of result that you’re looking for? Below is the screenshot provided for the reference -

To achieve this result, firstly I added a date table into the model and marked the date column as a “Date Table” and then created a direct relationship between the Source.Name and Date column and then dragged the date field from date table to the visual by replacing it with the Source.Name.

I’m also attaching the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Pbi_dset - Harsh.pbix (129.0 KB)

2 Likes

I had to adjust this a bit, but this was a great tip.

I already had a calendar table with the relationship between ‘Calendar’ [Date] and ‘Pbi’ [End Date (Ops)]. I have added another inactive relationship between ‘Calendar’ [Date] and ‘Pbi’ [Source.Name].

My final measure is:

Sales Budget (Risk) Trend = 
CALCULATE( SUM( Backlog[Sales Budget] ) , 
    ALL( Backlog[ReportRank] ),
    USERELATIONSHIP(Backlog[Report Date],'Calendar'[Date]))

Backlog table = Pbi table
Report Date = Source.Name

thank you @Harsh ! :slight_smile:

Hello @marek.regulski,

You’re Welcome. :slightly_smiling_face:

I’m glad that you found it helpful and was able to assist you.

Thanks and Warm Regards,
Harsh