Filtering model on latest version of all attributes from all dimensions through DAX

[ramiouaness]

Hello,

I have a model that's comprised of multiple tables containing, for every ID, multiple rows with a valid_from and valid_to dates.

This model has one table in that is linked to every other table (a table working as both a fact and a dimension).

This fact has bi-directional cross filtering with the other tables.

I also have a date dimension that is not linked to any other table.

I want to be able to calculate the sum of a column in this table in the following way:

- If a date range is selected, I want to get the sum of the latest value per ID from the fact able that is before the max selected date from the date dimension.

- If no date is selected, I want to get the sum of the current version of the value per ID.

This comes down to selecting the latest value per ID filtered on the dates.

Because of the nature of the model (bi-directional with the fact/dimension table), I want to have the latest version of any attribute from any dimension selected in the visual.

Here's an data example and the desired outcome:

fact/dimension table:

ID	Valid_from	Valid_to	Amount	SK_DIM1	SK_DIM2
1	01-01-2020	05-12-2021	50	1234	6787
1	05-13-2021	07-31-2021	100	1235	6787
1	08-01-2021	12-25-2021	100	1236	6787
1	12-26-2021	12-31-2021	200	1236	6787
1	01-01-2022	12-31-9999	200	1236	6788

Dimension 1:

ID	SK	Valid_from	Valid_to	Name
1	1234	10-20-2019	06-01-2021	Name 1
1	1235	06-02-2021	07-31-2021	Name 2
1	1236	08-01-2021	12-31-9999	Name 3

Dimension 2:

ID	SK	Valid_from	Valid_to	Name
1	6787	10-20-2019	12-31-2021	Name 1
1	6788	01-01-2022	12-31-9999	Name 2

My measure is supposed to do the following:

- If no date is selected than the result will be a matrix like the following:

Dim 1 Name	Dim 2 Name	Amount Measure
Name 3	Name 2	200

- If July 2021 is selected than the result will be a matrix like the following:

Dim 1 Name	Dim 2 Name	Amount Measure
Name 2	Name 1	100

So the idea here is that the measure would filter the fact table on the latest valid value in the selected date range, and then the bi-directional relationships will filter the dimensions to get the corresponding version to that row with the max validity (last valid row) in the selected range date.

I have tried to do the following two DAX codes but it's not working:

Solution 1:

Amount Measure=
VAR _maxSelectedDate = MAX(Dates[Dates])
VAR _minSelectedDate = MIN(Dates[Dates])
VAR _maxValidFrom = 
    CALCULATE(
        MAX(fact[valid_from]),
        DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate)
        || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate)
    )

RETURN
CALCULATE(
    SUM(fact[Amount]),
    fact[valid_from] = _maxValidFrom
)

     

Solution 2:

Amount Measure=
VAR _maxSelectedDate = MAX(Dates[Dates])
VAR _minSelectedDate = MIN(Dates[Dates])
VAR _maxValidFromPerID = 
    SUMMARIZE(
        FILTER(
            fact,
            DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate)
            || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate)
        ),
        fact[ID],
        "maxValidFrom",
        MAX(fact[valid_from])
    )

RETURN
    CALCULATE(
        SUM(fact[Amount]),
        TREATAS(
            _maxValidFromPerID,
            fact[ID],
            fact[valid_from]
        )
    )

Now the problem here is that with the first solution, filtering on other dimension work and I get the last version in the selected date range for all attributes of all used dimensions. But the problem here is that the max valid from is not calculated per ID, so I only get the max valid from overall.

With the second solution, I do get the right max valid from per ID and the resulting number is correct, but for some reason, when I use other attributes from the dimensions, it duplicates the amount for every version of that attribute.

So if somebody can explain this behaviour that will be great, and also, more importantly, if you have any solution to have both the latest value per ID and still keep filtering on other attributes, that would be great!

Sorry for the long post, but I thought it's best to give all the details for a complete understanding of my issue, this has been picking my brain since few days now and I'm sure I'm missing something stupid but I turned to this community for help because I cannot seem to be able to find a solution!

Thank you very much in advance for any help!

[v-chenwuz-msft]

Hi @ramiouaness ,

It seems a bit complicated. Please try to expand the date. Convert the fact table to record the status of each id for each day. 

You can refer this link.

Fill dates between dates with Power BI / Power Query — The Power User

Expand the continuous date interval - Microsoft Power BI Community

Power bi is better suited to a star structure, and perhaps combining dim1 and dim2 and the fact table into one would make it easy to do so.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.