Forum Discussion

RomanDautov88's avatar
RomanDautov88
New Member
4 years ago
Solved

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

SF8888888801.12.202115:47:0010
SF8888888801.12.202115:48:009
SF777777702.12.20219:48:008
SF777777703.12.20213:48:007
SF6666666601.12.20219:47:006
SF5555555501.12.20213:47:005
SF44444444401.12.20219:47:008
SF44444444401.12.20213:47:007
SF33333333301.12.202115:47:0010
  • Anonymous's avatar
    Anonymous
    4 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

  • Anonymous's avatar
    Anonymous
    Not 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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity 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]<>"")