Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Distinct in Grouping Table

Hi,
I make a summarized pivot table and I need to count distinct sessions by track:

Distinct_Sessions =
Countx(
Filter (
SUMMARIZE('FactEvents', 'FactEvents'[Date],FactEvents[SESSION_ID],'FactEvents'[Page],
"Display",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="widget display" ),
"Click",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="manual_widget_click") ,
"Guest",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="widget_guest_login" ),
"Save",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="guest_save_profile" )
),
[Display]<>"" &&[Click]<>""&&[Guest]="" && [Save]=""
),
?????

So I need to count distinct sessions where :Display<>"" &&Click<>""&&Guest="" && Save="" 
And so for every variation.....

The problem that distinctcount is not accept grouping tables..

  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try like

    Countx(
    Filter (
    SUMMARIZE('FactEvents', 'FactEvents'[Date],FactEvents[SESSION_ID],'FactEvents'[Page],
    "Display",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="widget display" ),
    "Click",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="manual_widget_click") ,
    "Guest",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="widget_guest_login" ),
    "Save",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="guest_save_profile" )
    ),
    not(isblank([Display])) && not(isblank([Click])) && not(isblank([Guest])) && isblank([Save])
    ),[SESSION_ID]
    )

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try like

    Countx(
    Filter (
    SUMMARIZE('FactEvents', 'FactEvents'[Date],FactEvents[SESSION_ID],'FactEvents'[Page],
    "Display",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="widget display" ),
    "Click",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="manual_widget_click") ,
    "Guest",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="widget_guest_login" ),
    "Save",CALCULATE(FIRSTNONBLANK('FactEvents'[Event Action],1),'FactEvents'[Event Action]="guest_save_profile" )
    ),
    not(isblank([Display])) && not(isblank([Click])) && not(isblank([Guest])) && isblank([Save])
    ),[SESSION_ID]
    )