Measure to count a value from different tables with differing date relationships (pls read)

[D_PBI]

Hi,
I have below DAX that creates a table. The Financial Year variables are calculated in Power Query. For example, the __FYS will be fixed at 01/08/2023, __FYE will be 31/07/2024. The __PFYS and __PFYE will be 01/08/2022 and 31/07/2023, respectively.

The fields, from resulting table above, are dragged into a table visual as shown below.

I now am required to allow the report user to select the year(s) through a slicer visual and that year(s) be part of the above calculation. The slicer visual will have the years from the Date dimension present that filters the Agreement table. There are two relationships between the Date dimenson and the Agreement table - these being dimDate[Date] <<>> agreement[Execution Date] (this is the active relationship), and dimDate[Date] <<>> agreement[Received Date] (this is the inactive relationship).

Instead of the measures being dragged into a Table visual, this time they will placed in a Matrix visual but only a single measure will show at a time. What I mean by this is the report user will have use a filter visual that drops down the list of the four measures - the same measures that are included in the above DAX. The Matrix visual contains a measure, and this measure's checks which drop down measure was selected and shows that actual measure value in the Matrix value. Hope that's clear.

Below is my initial attempt at the DAX needed. My thought is I'd create the Calculate table for the 'Funding Applications' measure. Once this table is created, I'll create a measure to actually distinct count the number of agreementids (which is the figure that represents the Funding Applications). It is this top measures that appears in the Matrix visual should the report user have chosen 'Funding Applications' from the drop down list. This process will be the same for the other measures that the report user has the choice to select. The Matrix visual is further below.

This the drop down the report user can select from.

The Matrix the measure is shown in is below. The header for the measure won't show as 'Funding Applications' (please use 'Funding Applications' and 'Patent Applications' interchangibly) as the measure header needs to be generic as, depending on what the report user selects, it will show a different measure.
Please also not the measure needs to be clever to calculate the distinct count of agreementid based on if the Matrix visual has been expanded to show just the year only, down to the quarter, or down to the month. ALl date nodes are from the dimDate dimension table.

I hope this post makes sense. Can someone help guide me on best way to achieve my aim? Thanks.

[v-csrikanth]

Hi @D_PBI 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @D_PBI 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

HI @D_PBI 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @D_PBI 
Here’s a single DAX measure that will switch between your four calculations based on the slicer selection and correctly honor whichever Date→Agreement relationship (Execution vs Received) or period comparison you need:
Dynamic Metric =
VAR Selected = SELECTEDVALUE( 'Measure Selector'[MeasureName], "Funding Applications" )
VAR BaseFilter =
agreement[Agreement Type] = 100000024 &&
(
CONTAINSSTRING( agreement[Agreement Status], "Preliminary" ) ||
CONTAINSSTRING( agreement[Agreement Status], "Full Application" ) ||
CONTAINSSTRING( agreement[Agreement Status], "Submitted" )
)
RETURN
SWITCH(
TRUE(),
Selected = "Funding Applications",
CALCULATE(
DISTINCTCOUNT( agreement[agreementid] ),
USERELATIONSHIP( 'Date'[Date], agreement[Execution Date] ),
BaseFilter
),
Selected = "Patent Applications",
CALCULATE(
DISTINCTCOUNT( agreement[agreementid] ),
USERELATIONSHIP( 'Date'[Date], agreement[Received Date] ),
BaseFilter
),
Selected = "Potential Value",
CALCULATE(
SUM( agreement[Value] ),
USERELATIONSHIP( 'Date'[Date], agreement[Execution Date] ),
BaseFilter
),
Selected = "Potential Value (Prev Year)",
CALCULATE(
SUM( agreement[Value] ),
TREATAS(
SAMEPERIODLASTYEAR( VALUES( 'Date'[Date] ) ),
agreement[Execution Date]
),
BaseFilter
),
BLANK()
)

Place this single measure in your matrix, use the Date hierarchy for rows (Year→Quarter→Month) and the disconnected Measure Selector slicer to drive the value shown.

1. USERELATIONSHIP activates the inactive relationship on‐the‐fly
2. TREATAS( SAMEPERIODLASTYEAR(...) ) lets you show last-year values against your current context
3. SELECTEDVALUE picks the user’s measure choice from the slicer

This one‐measure approach scales as you add or remove measure names—no new DAX objects are needed.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[D_PBI]

@techies - thanks but it's not working. I can't see the wood for the trees at the moment. I think, really, I may need someone to re-write my opening DAX, which takes the dates from Power Query parameters, to use the dates from the date dimension in the slicer visual. I'm too confused at present to even breakdown the need. I need to step away from it.

[techies]

Hi @D_PBI 

as you already have a disconnected table for the measure selector, a dynamic measure can be used to switch between the various calculations based on the user's selection.

Here's an example

Dynamic Metric =

VAR SelectedMeasure = SELECTEDVALUE('Measure Selector'[Measure Name])

VAR CommonFilter =

    FILTER(

        ALL('agreement'),

        agreement[Agreement Type] = 100000024 &&

        (

            CONTAINSSTRING(agreement[Agreement Status], "Preliminary") ||

            CONTAINSSTRING(agreement[Agreement Status], "Full Application") ||

            CONTAINSSTRING(agreement[Agreement Status], "Submitted")

        )

    )

RETURN

SWITCH(

    TRUE(),

    SelectedMeasure = "Funding Applications",

        CALCULATE(

            DISTINCTCOUNT(agreement[agreementid]),

            USERELATIONSHIP('date'[Date], agreement[Execution Date]),

            CommonFilter

        ),

    SelectedMeasure = "Potential Value",

        CALCULATE(

            SUM(agreement[Value]),

            USERELATIONSHIP('date'[Date], agreement[Execution Date]),

            CommonFilter

        ),

    BLANK()

)

[D_PBI]

@techies thank you for your help. I shall try this approach but I think I also need help on the date filtering logic. The DATESBETWEEN and SAMEPERIODLASTYEAR functions don't seem to be doing as I need. Do I have the use of these functions wrong with their intended use against various nodes of the dimDate table in the Matrix (i.e. Year, Quarter, Month)?

[techies]

Hi @D_PBI i guess you can modify your dynamic metric to include previous year comparisons using SAMEPERIODLASTYEAR like this ---- 

VAR PY_Dates = SAMEPERIODLASTYEAR(VALUES('date'[Date]))

and then using it inside TREATAS() to filter the fact table (agreement[Execution Date]) based on the previous year's date

 SelectedMeasure = "Potential Value (Previous Year)",

        CALCULATE(

            SUM(agreement[Value]),

    TREATAS(PY_Dates, agreement[Execution Date]),

            CommonFilter

        ),