Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
2 years ago
Solved

DAX SUM query

Hi Community 
I have the following model in desktop


Here is an example of the facts table. 

 

My Stock dimension table connects to the fact table using the UPRN field.   The Stock dimension table has a field called 'Tenants affected' here is an example

 Ony my report page I would like to display in a card visual the number of 'tenants affected'  however due to the fact table containing mutiple 'UPRN's the DAX function I am try to write SUMS all 'Tenants' for each of the mutiple UPRNs

 

As shown in the example below my DAX is outputting 11

UPRNTenants Affected
181
181
263
263
263

 

Where as I need it to output 4

UPRNTenants Affected
181
  
263


Thank you

Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  cottrera ,

    Thanks for your feedback. If you still want to get it, you can create antoher new measure as below and replace the original measure [Members Affected] on the visual.

    Measure = SUMX ( VALUES ( 'DIM_Stock'[UPRN] ), [Members Affected] )

    Best Regards

5 Replies

  • cottrera's avatar
    cottrera
    Post Prodigy
    2 years ago

    Hi v-yiruan-msft & sjoerdvn by company has now decided they no longer need the insight that I was working on . Therefore the measure I required is no required. Thank you for your help

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi  cottrera ,

      Thanks for your feedback. If you still want to get it, you can create antoher new measure as below and replace the original measure [Members Affected] on the visual.

      Measure = SUMX ( VALUES ( 'DIM_Stock'[UPRN] ), [Members Affected] )

      Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi cottrera ,

    Thanks for reaching out to us about your problem. Base on my research, it seems that you want to get the count of 'tenant effected' and display it on the card visual. In order to make further troubleshooting and give you a solution shortly, could you please provide the following info? It would be very helpful to find the solution. Thank you.

    • Are the field [Members Affected] and 'tenant effected' the same?
    • Is the field[Members Affected] a measure or fact field in the table 'Stock'? If it is a measure, could you please provide the related formula?

            

    • What's the formula of [Tenants Affected]?
    • How did you set the following output? Is the field [UPRN] from stock dimension table or facts table?
      UPRN Tenants Affected
      18 1
      18 1
      26 3
      26 3
      26 3

    Best Regards

  • cottrera's avatar
    cottrera
    Post Prodigy
    2 years ago

    Hi thank you for responding so quickly.

     

    Members and Tenants affected are the same (appologies) The members figures is a measure that points at  DIM_Stock table 

    Members Affected = SUM(Stock[Members Affected])
     
    The UPRN is from theDIM_Stock table 

     

    thank you

  • sjoerdvn's avatar
    sjoerdvn
    Solution Sage
    2 years ago

    I think you want to use a measure like this (note that you'll have to adapt to the proper table and column names, as these are not entirely clear from your information):

    # tenants affected =CALCULATE(SUM(Stock[Members Affected]), CROSSFILTER(Stock[UPRN],RepairsFact[UPRN], Both))