Table summary - desired calcuation possible in DirectConnect?

[RMDNA]

We have a list of events, and each event has a set of dates attached to it for when it was run.

Event:                Dates:          

Event A               01/01/2018          

Event A               01/02/2018          

Event B               01/03/2018          

Event C               01/04/2018          

Event C               01/05/2018          

Event C               01/06/2018          

Event D               01/07/2018          

What I'd like to do is get the duration of the events:

Event:         Duration:

Event A        2 days

Event B        1 day

Event C        3 days

Event D        1 day

And then create a chart which sums up the count of events of by duration:

Duration:       Number of Events:

1 day              2

2 days            1

3 days            1

This is direct connect to a cube, so no custom columns or groups. Import is off the table. Any help?

[Ashish_Mathur]

Hi,

Here's one idea.

1. Using the Query Editor, Group by events and perform a distinctcount of the dates
2. Load that data into the Data Model
3. Create a visual with Days (computed in step 1 above) in the row labels
4. Write this DAX formula

=COUNTA(Data[Event])

Hope this helps.

[v-huizhn-msft]

Hi @RMDNA,

Please create a calculated column using the formula.

Duration =
CALCULATE (
    DATEDIFF ( MIN ( Table1[Dates] ), MAX ( Table1[Dates] ), DAY ),
    ALLEXCEPT ( Table1, Table1[Event] )
)
    + 1

Then create measure using the formula.

Number of Events = DISTINCTCOUNT(Table1[Event])

Please see expected result as follows, you can download attachment file for more details.


Best Regards,
Angelia

[RMDNA]

@v-huizhn-msft, unfortunately, this is a live connection, so I can't create calculated columns. Is there any other solution?

[v-huizhn-msft]

Hi @RMDNA,

What's your data source, I can create a calculated column when I connect SQL Server in Direct Mode.



Best Regards,
Angelia

[RMDNA]

@v-huizhn-msft, @Ashish_Mathur - It's a live connection to an Analysis Services cube. I'm unable to access the Query Editor or created calculated columns; I can only create measures.

[v-huizhn-msft]

Hi @RMDNA,

Yes, we can not create calculated column when live connect to SSAS, which a limitation, you'e better use import mode.

Best Regards,
Angelia