Apply filter to a measure outside of model relationship

[mannisobrien]

Hi-

I have a Date Table that I am building my measures from to aggregate daily/weekly info.

I have joined a table that contains information about the Go-Live Date and # of Chargers for a given Site:

Site Name | # of Plugs | Go-Live Date

In my Date Table I have successfully created a measure to calculate Live Plugs by day:

Live Plugs = 
CALCULATE (
    SUM('Sites-PiS'[Plugs]),
    FILTER (
        'Sites-PiS',
        'Sites-PiS'[Go-Live Date] <= 'Date Table'[Date]
    )
)

Here is the result in the report:

My issue is that if I apply a Site Name filter in the report, it does not apply to the Live Plugs measure. That is because my relationship in the model looks like this:

Date Table[Date] = Site Table [Go-live Date]

So when I click different Site Names in my report, nothing changes in Live Plugs. I want to be able to click Site Name filter and it the weekly report show only live plugs for the selected Site

Any ideas?

thanks

[mannisobrien]

Hi Both @Wilson_ and @adudani  Please see my pbix file here:

https://drive.google.com/file/d/1UdFiNEr-YhiK6SUVKqoiSKNG2uIM8aQn/view?usp=drive_link

thanks for the help!

[mannisobrien]

thanks @adudani but I don't think this works. Not sure how to upload files so here is some more detail:

Here's my Sites-PiS table:

Go-Live Date	Site Name	Plugs
12/29/2023	Site 1	6
12/29/2023	Site 2	6
12/29/2023	Site 3	6
12/30/2023	Site 4	6
2/7/2024	Site 5	6
11/16/2023	Site 6	8
1/29/2024	Site 7	6
2/16/2024	Site 8	6
2/29/2024	Site 9	6
2/16/2024	Site 10	6
2/29/2024	Site 11	6
2/29/2024	Site 12	6
2/27/2024	Site 13	6

Here's my report where I have the Site table which should filter the Live Plugs by Date table:

And again my model is set up with a Date Table joined to Sites-PiS like

Date Table[Date] = Sites-PiS[Go-Live Date]

How do I get a Live Plugs measure that takes into

account this Site Name Filter?

Live Plugs is calculated shown below. I have it as both a column calc and a measure.

Live Plugs =

CALCULATE (

    SUM('Sites-PiS'[Plugs]),

    FILTER (

        'Sites-PiS',

        'Sites-PiS'[Go-Live Date] <= 'Date Table'[Date]

    ))

[Wilson_]

Hi mannisobrien,

If I'm understanding your data model correctly, I see no reason that shouldn't work for you. I have one thought though. Check out this resource from Microsoft on editing interactions and see if your site name table is correctly set up to affect your other table.

Long story short, when you're showing the interactions between visuals and you have the right table selected, you should see something line the circled above the left table and the highlighted icon should be selected.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[mannisobrien]

Thanks @Wilson_ unfortunately that isn't working either. I think it has to do with the fact that my measure makes no reference to the Site Name field? 

Live Plugs =

CALCULATE (

    SUM('Sites-PiS'[Plugs]),

    FILTER (

        'Sites-PiS',

        'Sites-PiS'[Go-Live Date] <= 'Date Table'[Date]

    ))

Do I have to add a line here that triggers when the Site Name filter is applied?

[Wilson_]

mannisobrien,

Your issue is much simpler than you thought. The problem is you did not actually create a measure; you created a calculated column. 🙂

Try this as your measure:

Live Plugs = 
VAR MaxDate = MAX ( 'Date Table'[Date] )
VAR FilteredDates =
FILTER (
    ALL ( 'Date Table' ),
    'Date Table'[Date] <= MaxDate
)
VAR Result =
CALCULATE (
    SUM ( 'Sites-PiS'[Plugs] ),
    FilteredDates
)

RETURN Result

The main tweaks I made were adding a variable to capture the date (because you have to in a measure where you don't have row context) and filtering the Date Table (your dimension table) instead of the Sites-PiS table (your fact table).

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[adudani]

hi @mannisobrien ,

try using :TREATAS function - DAX | Microsoft Learn

reference: TREATAS - DAX Guide (youtube.com)

if this doesn't work, kindly provide sample input and output, masking sensitive data.