topic Re: DAX GROUP BY values with filter and condition in DAX Commands and Tips

DAX GROUP BY values with filter and condition

RomanDautov88 — Mon, 13 Dec 2021 20:26:21 GMT

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

Re: DAX GROUP BY values with filter and condition

smpa01 — Mon, 13 Dec 2021 20:24:57 GMT

RomanDautov88 provide sample data and expected output.

Re: DAX GROUP BY values with filter and condition

Anonymous — Wed, 22 Dec 2021 06:49:27 GMT

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

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

Best Regards

Re: DAX GROUP BY values with filter and condition

wdx223_Daniel — Wed, 22 Dec 2021 08:33:36 GMT

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]<>"")