Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Hi,
Here's one idea.
=COUNTA(Data[Event])
Hope this helps.
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
@v-huizhn-msft, unfortunately, this is a live connection, so I can't create calculated columns. Is there any other solution?
Hi @RMDNA,
What's your data source, I can create a calculated column when I connect SQL Server in Direct Mode.
Best Regards,
Angelia
@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.
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
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |