Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RMDNA
Solution Sage
Solution Sage

Table summary - desired calcuation possible in DirectConnect?

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?

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-huizhn-msft
Employee
Employee

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.

2.PNG
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.

1.PNG

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.

 

a.PNG

 

b.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.