Create a measure for no activity in last 30 days

[deedeedudu]

Hi team,

I want to create a measure to consolidate the list of Enterprise that have not shown any activity in the last 30 days.

I have following tables and the data model has a link of "created_date", date, "Creation_date" to Calendar table date field.

Similarly, there's a relation of "Enterprise_name of tables AA, AAA, BB to DDD

Now, there should be measure that can be displayed as a card containing the list of customers that have sent =0 for tables, AA and AAA

and Created = 0 for table BB

So in the above example the list would be

ggg

[V-lianl-msft · Accepted solution]

Hi @deedeedudu ,

You can try to merge query in the query editor first, which will be easier to create measure.

let
    Source = Table.NestedJoin(DDD, {"Enterprise_name "}, AAA, {"Enterprise_name "}, "AAA", JoinKind.LeftOuter),
    #"Expanded AAA" = Table.ExpandTableColumn(Source, "AAA", {"Sent", "Created_date"}, {"AAA.Sent", "AAA.Created_date"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded AAA", {"Enterprise_name "}, AA, {"Enterprise_name "}, "AA", JoinKind.LeftOuter),
    #"Expanded AA" = Table.ExpandTableColumn(#"Merged Queries", "AA", {"Sent", "Created_date"}, {"AA.Sent", "AA.Created_date"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded AA", {"Enterprise_name "}, BB, {"Enterprise_name "}, "BB", JoinKind.LeftOuter),
    #"Expanded BB" = Table.ExpandTableColumn(#"Merged Queries1", "BB", {"Created", "Created_date"}, {"BB.Created", "BB.Created_date"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded BB", {"AA.Created_date", "BB.Created_date", "AAA.Created_date"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Date"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each Date.IsInPreviousNDays([Date], 30)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"AAA.Sent", "AA.Sent", "BB.Created"}),
    #"Merged Queries2" = Table.NestedJoin(#"Removed Columns1", {"Enterprise_name "}, DDD, {"Enterprise_name "}, "DDD", JoinKind.RightOuter),
    #"Expanded DDD" = Table.ExpandTableColumn(#"Merged Queries2", "DDD", {"Enterprise_name "}, {"DDD.Enterprise_name "}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded DDD",{"Enterprise_name "})
in
    #"Removed Columns2"

The data after processing is like this.

Then you can create a measure like below:

Measure = CONCATENATEX(FILTER(Merge1,ISBLANK(Merge1[Date])),Merge1[DDD.Enterprise_name ],"-")

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

[deedeedudu]

Hi,

I've still not found the solution that I was looking for. Please provide suggestions. 

[amitchandak]

@deedeedudu , Join all three of them with a common date table , and common dimesion enterprise

have three measures like

_1 = CALCULATE(Sum(AA[Send]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

_2 = CALCULATE(Sum(AAA[Send]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

_3 = CALCULATE(Sum(BB[Send]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

Now if one of them is not there in last 30

countX(values(enterprise[enterprise]) , if( isblank([_1]), [enterprise], blank()) )

check all three not there in last 30

countX(values(enterprise[enterprise]) , if( isblank([_1]) && isblank([_2]) && isblank([_3]) , [enterprise], blank()) )

plot with enterprise from common table

[deedeedudu]

Thanks for replying. I created the three measures but in the end i don't need a count, rather i need a list , more on the lines of function CONCATENATEX. Can you help with that?