Filter a measure using selected value

[Anonymous]

Hello everybody,

I need your help since I wrapped my head around this for some time now...

I need to display only the values from a certain group, only the new ones in a certain selected week.

For example, main Data

CreatedDate	AppName	Group	WeekFloor	WeekCeiling
18/06/2020	A	prod	15/06/2020	21/06/2020
15/05/2020	B	prod	11/05/2020	17/05/2020
23/04/2020	C	prod	20/04/2020	26/04/2020
20/04/2020	D	prod	20/04/2020	26/04/2020
31/03/2020	A	prod	30/03/2020	05/04/2020
29/03/2020	B	prod	23/03/2020	29/03/2020
24/03/2020	B	test	23/03/2020	29/03/2020
15/03/2020	A	test	09/03/2020	15/03/2020
10/03/2020	C	test	09/03/2020	15/03/2020
06/03/2020	A	test	02/03/2020	08/03/2020

I want to display the app and its earliest date it entered in the Prod group.

for example, if I select WeekFloor = 23/03/2020, the measure should display "29-03-2020: B", because the app named B entered Prod first time on 29-03-2020.

If I select weekFloor = 20/04/2020, the measure should display "20-04-2020: D" and "23-04-2020: C", because the apps C and D entered Prod during that week.

If I select weekFloor = 15-06-2020, the measure should display blank, because app A entered Prod on 31-03-2020.

I tried multiple solutions, but without success...because they display all the apps, not only the ones within the selected week.

apps-to-prod = 
var week_start = SELECTEDVALUE(Data[WeekFloor])
var week_end = LOOKUPVALUE(Data[WeekCeiling],Data[WeekFloor],week_start)
var result = CALCULATE(
   CONCATENATE(VALUES(Data[CreatedDate]),
               CONCATENATE(":", VALUES(Data[AppName])),
   FILTER((Data), 
           Data[CreatedDate]>=week_start && 
           Data[CreatedDate]<=week_end && 
           Data[Group] == "prod")
  )
return result

It just doesnt seem to work 😞

I would highly appreciate your help!

Thank you,

Daniela

[Icey · Accepted solution]

Hello @dcraciun ,

Here's my solution. First I created a calculated column named Earliest which is to choose the oldest CreatedDate based on AppName and Group. Where you will use the ALLEXCEPT function. Then I created a measure called apps-to-prod that is to show the result. Where you will use the CONCATENATEX function, which is more convenient here than the CONCATENATE function.

The Oldest Column:

Earliest =
CALCULATE (
    MIN ( Data[CreatedDate] ),
    ALLEXCEPT ( Data, Data[AppName], Data[Group] )
)

The measurement of prosa applications:

apps-to-prod =
CONCATENATEX (
    FILTER (
        Data,
        'Data'[CreatedDate] = 'Data'[Earliest]
            && 'Data'[Group] = "prod"
    ),
    Data[Earliest] & ":" & Data[AppName],
    ","
)

Here are the results of my own test:

More details can be found here.

Best regards

Icey

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

[Anonymous]

@Icey 
Hello and THANK YOU VERY VERY VERY MUCH!!

You saved my last neurons from dying!

The Earliest column was the answer.

I managed to edit my solution using your indications, and it works!

Thank you, thank you, thank you!

Daniela

[amitchandak]

@Anonymous, Create a new column like this in your table and use that in display in the table

new column =[WeekCeiling] & ": " & [AppName]

[Anonymous]

@amitchandak 

Hello and thank you for your response.

Creating that column doesn't display the apps that entered prod during the selected week, but all the apps.

CreatedDate also implies any changes made to the app, that why I need something that involves earliest date, because the earliest CreatedDate is the first date when an app entered Prod.

The earliest CreatedDate of an app must be between selected WeekFloor and WeekCeiling, only then display the app.

Im so sorry if I get you confused, I dont know how to explain....

It's something like this: If i could use the selected Weekceiling and Weekfloor as filters for the Creation Date.

Thank you,

Daniela

[lbendlin]

Define what you mean by "the measure".  Is it not sufficient to apply your filters to a standard table visual that has App names  with a slicer that has week bottoms? What are you planning to do next?

[Anonymous]

@lbendlin 

Hello and thank you for your response!

I have another visual that shows the weekly trend line for the number of apps.

On x axis it has the week ceiling, on Y axis is has the number of apps.

I want so select the week ceiling from the x axis of that visual, and therefore the selection to update the measure i'm struggling with.

By measure I mean the formula that will display the app who entered prod in the selected week.

Is it clearer? I don't know to explain...

This is the weekly/monthly trend line. If I select a date from it, it should update the visual (most likely a card) and display the app that entered prod during that week.

 Thanks,

Daniela