Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI Aggregation Tables and Distinct Count a value

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.

 

 

 

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , For distinctcount you have keep unaggregated Table only.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi @v-yingjl Mows @amitchandak

    Please find the data below

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

    StopNumberTripNumberBilldateOrderLocationCodeStopActualMilesWeight1DriveTimeHours
    496889887789011888610/3/202055555007.1666
    496889897789011888610/3/20205555511295716.9166
    496889907789011888610/3/20205555518158356.6666
    496889917789011888610/3/2020555559207.75
    496889927789011888710/3/202055555009.5333
    496889937789011888710/3/2020555552885348.8333
    496889947789011888710/3/2020555551358258.9666
    496889957789011888710/3/2020555559129229.5333
    496889967789011888710/3/2020555552751699.3333
    496889977789011888710/3/20205555568010.1
    496889987789011888810/3/202055555007.5
    496889997789011888810/3/2020555558057116.45
    496890007789011888810/3/20205555510667547.0833
    496890017789011888810/3/2020555551707.5
    496890027789011888910/3/202055555005.7666
    496890037789011888910/3/2020555556793235.0666
    496890047789011888910/3/2020555552806.2666
    496890057789011889010/3/2020555550012.8
    496890067789011889010/3/202055555111327712.2
    496890077789011889010/3/20205555523700312.3666
    496890087789011889010/3/20205555575541812.1666
    496890097789011889010/3/202055555194012.9166
    496890107789011889110/3/202055555008.55
    496890117789011889110/3/2020555558357497.3833
    496890127789011889110/3/202055555265467.55
    496890137789011889110/3/202055555386358.65
    496890147789011889110/3/2020555558909.55

    OrderLocationCodeLocation
    55555Apple Sales

    • v-yingjl's avatar
      v-yingjl
      Community Support
      5 years ago

      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.