Forum Discussion
DAX GROUP BY values with filter and condition
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 |
- Anonymous4 years ago
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
3 Replies
- AnonymousNot applicable
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
Community Champion
RomanDautov88 provide sample data and expected output.
- wdx223_Daniel
Community Champion
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]<>"")