DAX GROUP BY values with filter and condition

[RomanDautov88]

Hi everyone!

Need help in Dax with this implementation:
What i have: 2 tables with "Date", "Time", "NPS" (scores), "Data source" tag (both table have same columns, diffirence in Data source tag)
What i need: grouping date/time/nps and if time in 1st source >= 2nd source i need with record key (combined text with Date/phone/time for filters later).

Having working M-code, but cant understand Dax variant...

 M-code:

let
Source = Table.Combine({IVR,SF}),
#"Grouped Rows" = Table.Group(Source,
{"fnPhone", "Date", "NPS"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Condition", each Table.RowCount(Table.SelectRows(_, each [Data Source] = "IVR")) > 0 and Table.RowCount(Table.SelectRows(_, each [Data Source] = "SF")) > 0, type nullable number},
{"IVR", each Table.SelectRows(_, each [Data Source] = "IVR"), type table [Time=nullable time]},
{"SF", each Table.SelectRows(_, each [Data Source] = "SF"), type table [Time=nullable time]}
}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Condition] = true)),
#"Expanded IVR" = Table.ExpandTableColumn(#"Filtered Rows", "IVR", {"Time"}, {"IVR.Time"}),
#"Expanded SF" = Table.ExpandTableColumn(#"Expanded IVR", "SF", {"Time"}, {"SF.Time"}),
#"Added Custom" = Table.AddColumn(#"Expanded SF", "Custom",
each if [SF.Time] >= [IVR.Time]
then null
else Text.Combine({Text.From([Date], "ru-RU"), Text.From([fnPhone], "ru-RU"), Text.From([IVR.Time], "ru-RU")}, "|")),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [Custom] <> null and [Custom] <> "")
in
#"Filtered Rows1"

Data example:

Data SourcefnPhoneDateTimeNPS

SF	88888888	01.12.2021	15:47:00	10
SF	88888888	01.12.2021	15:48:00	9
SF	7777777	02.12.2021	9:48:00	8
SF	7777777	03.12.2021	3:48:00	7
SF	66666666	01.12.2021	9:47:00	6
SF	55555555	01.12.2021	3:47:00	5
SF	444444444	01.12.2021	9:47:00	8
SF	444444444	01.12.2021	3:47:00	7
SF	333333333	01.12.2021	15:47:00	10

[wdx223_Daniel]

NewTable=FILTER(GENERATE(IVR,VAR _t=FILTER(SF,SF[fnPhone]=IVR[fnPhone]&&SF[Date]=IVR[Date]&&SF[NPS]=IVR[NPS]&&SF[Time]<IVR[Time]) RETURN ROW("Key", IF(COUNTROWS(_t),IVR[Date]&"|"&IVR[fnPhone]&"|"&IVR[Time]))),[Key]<>"")

[Anonymous · Accepted solution]

Hi @RomanDautov88 ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below in table IVR to get the count of record which meet the special conditions

IVRCount = 
COUNTROWS (
    FILTER (
        'SF',
        'SF'[fnPhone] = 'IVR'[fnPhone]
            && 'SF'[Date] = 'IVR'[Date]
            && 'SF'[NPS] = 'IVR'[NPS]
            && 'SF'[Time] < 'IVR'[Time]
    )
)

2. Create a calculated column  in table IVR or create a calculated table to get the record key

Record Key = IF ( [IVRCount] > 0, 'IVR'[Date] & "|" & 'IVR'[fnPhone] & "|" & 'IVR'[Time] )

Create calculated columns

Table = 
SUMMARIZE (
    FILTER ( 'IVR', 'IVR'[IVRCount] > 0 ),
    'IVR'[fnPhone],
    'IVR'[Date],
    'IVR'[NPS],
    'IVR'[Time],
    "Recordkey",
        'IVR'[Date] & "|" & 'IVR'[fnPhone] & "|" & 'IVR'[Time]
)

Create calculated table

Best Regards

[smpa01]

@RomanDautov88  provide sample data and expected output.