Power BI Aggregation Tables and Distinct Count a value

[Anonymous]

Hi,

I am currently setting up my first data model using Power BI Aggregation tables and am having a problem getting distinct count to work with it.  I seem to be able to get sums to work just fine but if I try to distinct count a value and add that to a table with other summed measures it uses a direct query instead of the aggregate tables.  However, if I separate the distinct count from the sums into 2 separate tables then they both hit the aggregate.  So I'm at a loss here.  

I am also having problems finding good articles on Power BI Aggregate tables since a lot of the results I get back are 'aggregations' like a group by.  Which isn't what I'm looking for.  

in manage aggregations i have it setup so the int version of my field is "Count", Points to my fact table, and then the final column is the string version of the column I am counting.  

Sorry I'm trying not to post an image since it has table names and such.

[Anonymous]

Hi @v-yingjl Mows @amitchandak

Please find the data below

I want to count different stops and trips, but add up the other values

StopNumber	TripNumber	Billdate	OrderLocationCode	StopActualMiles	Weight1	DriveTimeHours
49688988	77890118886	10/3/2020	55555	0	0	7.1666
49688989	77890118886	10/3/2020	55555	112	9571	6.9166
49688990	77890118886	10/3/2020	55555	18	15835	6.6666
49688991	77890118886	10/3/2020	55555	92	0	7.75
49688992	77890118887	10/3/2020	55555	0	0	9.5333
49688993	77890118887	10/3/2020	55555	28	8534	8.8333
49688994	77890118887	10/3/2020	55555	13	5825	8.9666
49688995	77890118887	10/3/2020	55555	91	2922	9.5333
49688996	77890118887	10/3/2020	55555	27	5169	9.3333
49688997	77890118887	10/3/2020	55555	68	0	10.1
49688998	77890118888	10/3/2020	55555	0	0	7.5
49688999	77890118888	10/3/2020	55555	80	5711	6.45
49689000	77890118888	10/3/2020	55555	106	6754	7.0833
49689001	77890118888	10/3/2020	55555	17	0	7.5
49689002	77890118889	10/3/2020	55555	0	0	5.7666
49689003	77890118889	10/3/2020	55555	67	9323	5.0666
49689004	77890118889	10/3/2020	55555	28	0	6.2666
49689005	77890118890	10/3/2020	55555	0	0	12.8
49689006	77890118890	10/3/2020	55555	111	3277	12.2
49689007	77890118890	10/3/2020	55555	23	7003	12.3666
49689008	77890118890	10/3/2020	55555	75	5418	12.1666
49689009	77890118890	10/3/2020	55555	194	0	12.9166
49689010	77890118891	10/3/2020	55555	0	0	8.55
49689011	77890118891	10/3/2020	55555	83	5749	7.3833
49689012	77890118891	10/3/2020	55555	2	6546	7.55
49689013	77890118891	10/3/2020	55555	3	8635	8.65
49689014	77890118891	10/3/2020	55555	89	0	9.55

OrderLocationCode	Location
55555	Apple Sales

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create this calculated table:

New Table =
SUMMARIZE (
    'Table',
    'Table'[StopNumber],
    'Table'[TripNumber],
    'Table'[Billdate],
    'Table'[OrderLocationCode],
    'Table'[Location],
    "Total_Stop",
        CALCULATE (
            SUM ( 'Table'[StopActualMiles] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        ),
    "Total_Miles",
        CALCULATE (
            SUM ( 'Table'[Weight1] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        ),
    "Total_Drive",
        CALCULATE (
            SUM ( 'Table'[DriveTimeHours] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[TripNumber] = EARLIER ( 'Table'[TripNumber] )
            )
        )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , For distinctcount you have keep unaggregated Table only.

[Anonymous]

This may be a stupid question but I am struggling with it.  

How do I join a unaggregated table back to my aggregated ones?  Since if I create one with a distinct count or list of the numbers i'm counting the arrow points in the wrong direction.  From the distinct one back to the agg.  

Do I need to have them all connect to the same dims and have them filter that way?

[Anonymous]

This linkw as helpful too

https://community.powerbi.com/t5/Desktop/distinct-count-of-multiple-columns/td-p/433060 

[v-yingjl]

Hi @Anonymous ,

Based on your description, not certain what is your expected output. Could you please consider sharing a dummy sample for further discussion without any fact information?

Sample data and expected output would help tremendously.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.