cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DanBusIntel Member
Member

How to count the number of groups (but not actually doing an aggregation on an additional column)?

Hi,

I have a table visual that looks like the below (it's been filtered in the report using a.rundate = 2017 & 2018):
a.srcmoduleid          a.rundate          Column3          Column4          Column5
English                       13/04/2018        5                       Yes                     3
English                       13/04/2018        1                       No                     3
English                       13/04/2018        3                       No                     14
English                       12/04/2018        2                       No                     2
English                       12/04/2018        2                       Yes                     13

English                       11/02/2017        1                       Yes                     1
Maths                         20/02/2018        44                    Yes                      22

So taking the above table visual contents (i.e. the filtered dataset in play at this point), I would like to just count the number of groups based on a.srcmoduleid and a.rundate. The rest of the columns are not required (I've just put them in the above so you know there are additonal columns). The value I'm looking for from the above is English = 3, and Maths = 1. I will look to place these values in a bar-chart.

 

I have produced the below bit of code thinkning it'll be the answer but it doesn't return what I'm looking for:

tbl_CountLoadsByGroupingOn_a.sourceid_And_a.rundate
          = SUMMARIZE(
                    Fact_Dataloads,
                    Fact_Dataloads[a.srcmoduleid],
                    Fact_Dataloads[a.rundate],
                   "TablesLoadedPera.rundateAndModuleID", DISTINCTCOUNT( Fact_Dataloads[a.rundate] )
                                  )
I'm thinking as the filter a.rundate is set in the background then those same fitlers will be applied to the SUMMARIZE function above.

 

The actual result I get from the above is:
a.srcmoduleid          a.rundate          TablesLoadedPera.rundateAndModuleID

English                      13/04/2018         25
English                      12/04/2018         25

English                      11/02/2017         25
Maths                        20/02/2018        16

 

It must be counting on a more granular basis than what I have scope for in my SUMMARIZE function. I was expecting 1 for each of the rows under the TablesLoadedPera.rundateAndModuleID column.
So I then set the TablesLoadedPera.rundateAndModuleID property in the Power bI table visual from 'Count' to 'CountDistinct' and it return the below:

a.srcmoduleid          a.rundate          TablesLoadedPera.rundateAndModuleID

English                      13/04/2018         1
English                      12/04/2018         1

English                      11/02/2017         1
Maths                        20/02/2018         1

I'm now thinking it's showing 1 under the TablesLoadedPera.rundateAndModuleID column as I have it to the granular level of a.rundate. So I remove a.rundate from the table visual and I can the below:

a.srcmoduleid          TablesLoadedPera.rundateAndModuleID

English                       1
English                       1

English                       1
Maths                         1

My end goal is to see the below:

a.srcmoduleid          TablesLoadedPera.rundateAndModuleID

English                       3
Maths                         1

 

I hop you understand what I'm trying to acheive. Please help?
Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
DanBusIntel Member
Member

Re: How to count the number of groups (but not actually doing an aggregation on an additional column

Please don't respond (look into my question) guys as I've managed to resolve it myself. I guess I just needed to step away from it to clear my eyes.

 

In case anyone is interested, the fix was a simple DAX statement:
NumberOfLoadsByModuleIDandRundate =
CALCULATE(
DISTINCTCOUNT( Fact_Dataloads[a.rundate] ),
GROUPBY( Fact_Dataloads,
Fact_Dataloads[a.srcmoduleid],
Fact_Dataloads[a.rundate]
)
)

 

Quite simple, but it always is when you know how  😉

 

View solution in original post

1 REPLY 1
DanBusIntel Member
Member

Re: How to count the number of groups (but not actually doing an aggregation on an additional column

Please don't respond (look into my question) guys as I've managed to resolve it myself. I guess I just needed to step away from it to clear my eyes.

 

In case anyone is interested, the fix was a simple DAX statement:
NumberOfLoadsByModuleIDandRundate =
CALCULATE(
DISTINCTCOUNT( Fact_Dataloads[a.rundate] ),
GROUPBY( Fact_Dataloads,
Fact_Dataloads[a.srcmoduleid],
Fact_Dataloads[a.rundate]
)
)

 

Quite simple, but it always is when you know how  😉

 

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 273 members 2,431 guests
Please welcome our newest community members: