Table column considering a slider filter

[kostal]

Hi,

I am trying to  find out if there is possible to create a column that will calculated its value dynamicly on based of a date slider filter. I tried lot of approches but no of them was successfull. I am considering if this is even possible.

But what I want to do. I have a table with column ID (not unique) and DateTime. I want to add next column isLast that will identify if a row has the latest DateTime of rows with the same ID. 

I tried to create a column like this:

isLast = CALCULATE(isblank(COUNTROWS(AllData)); FILTER(AllData; AND([DateTime] > EARLIER(AllData[DateTime]); [ID] = EARLIER([ID]))))

This works well, but it does not considering a date slider filter.

Can anyone help me, what am I doing wrong? Or is it even possible to do this?

Here is the source file: https://1drv.ms/u/s!Ag6JEwnU6D76m78KmEurRzSfFx0VDQ?e=pf6IUc

Tx

[v-frfei-msft]

Hi @kostal ,

Please create a measure as below instead of a calculated column.

Measure = 
VAR c =
    CALCULATE (
        COUNTROWS ( AllData ),
        FILTER (
            ALLSELECTED ( AllData ),
            AllData[DateTime] <= MAX ( AllData[DateTime] )
        ),
        VALUES ( AllData[ID] )
    )
VAR c_ =
    CALCULATE (
        COUNTROWS ( AllData ),
        ALLSELECTED ( AllData ),
        VALUES ( AllData[ID] )
    )
RETURN
    IF ( c = c_, TRUE (), FALSE () )

Pbix as attached.

[bheepatel]

Hi @kostal 

You are right - it is not possible because you have a calculated column (isLast) and you are trying to use a slicer to change the values in that column. This is not possible as you cannot use the slicer to change your data table.

Here is an alternative solution:

1. Create a measure called isLast (Measure) as below

isLast (Measure) = 

VAR vMaxDatePerID = CALCULATE(MAX(AllData[DateTime]), ALLSELECTED(AllData[DateTime]))
VAR vDatePerID = CALCULATE(MAX(AllData[DateTime]))

RETURN vMaxDatePerID = vDatePerID

2. In the visual table that you have, replace the isLast field with the isLast (Measure) as seen below in the screenshot. I have posted two screenshots (one for no date filter applied and one for date filter applied) and it works as required.

Hope this helps!

[kostal]

Hi bheepatel,

great, it works! It is unfortunate that it is not possibly to do it with column, but maybe I can live with this solution.

However. I am not sure how it works exactly. I do not see any mention about ID column and still the output is clustered through ID.

It probably is somehow connected to visual where when I remove the column ID it stops working:

Even when I add some other column it stops working. That is hardly usable for some more complicated use cases. 

Please, can you direct me, how it all works so I could find out solutions for other cases?

tx!

[bheepatel]

Hi @kostal 

You are right - it is connected to the visual and depends on which columns are inserted into the visual. 

To understand how it works, you need to understand how visual tables and measures work. If you had only placed the ID column in a table, you would get the table below. You would only get 4 rows where each row is a distinct record. You will not get the 8 rows that you have in your dataset.

ID

1

2

3

4

Let's say you now create a measure called MaxDate = Max(DateTime) and then add that to the table above. The model will then check what is the maximum date for each of those four IDs. What Power BI does, is that it looks at a distinct row and then calculates the maximum date for that distinct row. 

When you now add the DateTime field to the table above, you will see that you will now have 8 rows because all 8 rows are distinct i.e. there is distinct combination of ID & DateTime. And now if you add the MaxDate measure, then you will have 8 different results.

Similarly, if you add in a third column e.g. the Data column, and then add in the measure MaxDate, the model will look for a distinct combination of ID, DateTime & Data columns and then work out the maximum date for that combination.

I hope that makes sense?

Now in is Last (Measure) there is a variable called vMaxDatePerID. What this variable does is exactly the same function as the MaxDate measure above but it accounts for any filter applied on the DateTime field. To illustrate, look at the table below. Both the ID & Data fields have the same values. But the DateTime field is different so there are two unique rows. Therefore, you will have two different answers for the MaxDate measure as seen below.

ID	DateTime	Data	MaxDate
1	2020/01/01	Test	2020/01/01
1	2020/01/07	Test	2020/01/07

When you have no filter applied on the DateTime field, the vMaxDatePerID will be 2020/01/07 for both records.

When you have a filter applied, let's say 2020/01/01 - 2020/01/05, then the vMaxDatePerID will be 2020/01/01 because the vMaxDatePerID tries to the find the max values from what is selected (i.e. 2020/01/01 - 2020/01/05).

The key takeaway is that the MaxDate measure will look at ID, Data & DateTime fields to workout the max value (ignoring external filters on DateTime), but the vMaxDatePerID will look at the ID and Data fields to workout the max value (by accounting for the external filters on DateTime). 

I hope that helps?

[Pragati11]

Hi @kostal ,

Just modify your DAX to following:

isLast = CALCULATE(isblank(COUNTROWS(AllData)); FILTER(ALLSELECTED(AllData); AND([DateTime] > EARLIER(AllData[DateTime]); [ID] = EARLIER([ID]))))

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[kostal]

Hi @Pragati11 ,

tx for an answer. But unfortunately it does not works. I already tried this but without any noticebly impact on the output 😞