Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kostal
New Member

Table column considering a slider filter

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

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

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 () )

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
bheepatel
Resolver IV
Resolver IV

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.

 

1.JPG

 

2.JPG

 

 

Hope this helps!

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!

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.

 

IDDateTimeDataMaxDate
12020/01/01Test2020/01/01
12020/01/07Test2020/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
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 ,

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors