Hide lines when date from picker is greater than the fact

[MiKeZZa]

Hi amazing guys (and girls) that are helping people.... I'm a fanatic PowerBI user but I do have a problem that I can't fix now.

I have a dataset like this:

So what you see is that 1 cliënt can be on 1 day with more than 1 record, because of the costcenter (record 1+2) and the column 'lenght in days' gives the total length in days instead of the lenght at the specific day.

We are now looking for something like this:

So it must count the number of days that a person is on the queue. What we've done is added a dateslicer and these DAX-formulas:

unique clients = DISTINCTCOUNT('client'[client number])

unique number of days = DISTINCTCOUNT('fact'[client_date_unique])

So we count the distinct clients and the distinct number of clients per days.

Everything works great except when a cliënt is gone from queue.... When we take the dataset from above we get the following results:

6 okt 2014:

clientnumber         clients unique          number of days
1591                       1                              386
23897                     1                              379

7 okt 2014:

clientnumber         clients unique          number of days
1591                       1                              387
23897                     1                              380

8 okt 2014:

clientnumber         clients unique          number of days
1591                       1                              388
23897                     1                              380

But what we want:

clientnumber         clients unique          number of days
1591                       1                              388

So the problem is that because of the dateslicer cliënt 23897 is still on the queuelist, because of that he was here in past. But when somebody is gone from the queue on the enddate of the dateslicer he must be gone from the list.

We've tried many things but were not able to solve this untill now.

The .pbix with sample data, such as above is here: https://ufile.io/rx06o

I really hope that someone has an idea for us to solve this.

Ps; I'm also thinking about manipulating the 'length in days' column in source and display this one, but then we need to take the MAX() of this column, because of that 1 client can be 2 times on 1 date. And taking a MAX() gives other problems, such as a 'wrong' totalcolumn. So we would like to solve it with the current data if possible.

[v-shex-msft · Accepted solution]

Hi @MiKeZZa,

According to your description, you want to hide the records which last date is less than current selected date, right?
If this is a case, you can add conditions to check the last date.

unique clients = 
var clientId=LOOKUPVALUE(client[_clientid],client[client number],MAX('client'[client number]))
Return
IF(MAXX(FILTER(ALL('fact'),[_clientid]=clientId),[date])>=MAX('date'[datum]),DISTINCTCOUNT('client'[client number]),BLANK())

unique number of days = 
var clientId=LOOKUPVALUE(client[_clientid],client[client number],MAX('client'[client number]))
Return
IF(MAXX(FILTER(ALL('fact'),[_clientid]=clientId),[date])>=MAX('date'[datum]),DISTINCTCOUNT('fact'[client_date_unique]),BLANK())

Regards,

Xiaoxin Sheng

[MiKeZZa]

Now we've added our whole customer dimension (30.000 records) and it's now SO slow; it doesn't work. It gives memory errors or it takes minutes to load the visual where the measures are used.

I think this is because of that the lookupvalue is looping through the data.

Is there anything else possible to also make it work in our larger dataset..?

[v-shex-msft]

Hi @MiKeZZa,

I modified the fact table as variable, perhaps you can try it if it can improve the performance:

unique clients = 
var clientId=LOOKUPVALUE(client[_clientid],client[client number],MAX('client'[client number]))
var maxDate=MAXX(FILTER(ALL('fact'),[_clientid]=clientId),[date])
Return
IF(maxDate>=MAX('date'[datum]),DISTINCTCOUNT('client'[client number]),BLANK())

unique number of days = 
var clientId=LOOKUPVALUE(client[_clientid],client[client number],MAX('client'[client number]))
var maxDate=MAXX(FILTER(ALL('fact'),[_clientid]=clientId),[date])
Return
IF(maxDate>=MAX('date'[datum]),DISTINCTCOUNT('fact'[client_date_unique]),BLANK())

Regards,

Xiaoxin Sheng

[MiKeZZa]

It improved a little, but not good enough. We've now made a different solution with SQL. Thanks for all the help.

[MiKeZZa]

Hi @v-shex-msft,

I have to take some time to understand what you've writted but it works really great. Lovely!!!!