DAX SUM query

[cottrera]

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

UPRN	Tenants Affected
18	1
18	1
26	3
26	3
26	3

Where as I need it to output 4

UPRN	Tenants Affected
18	1
26	3

Thank you

[cottrera]

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 · Accepted solution]

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

[sjoerdvn]

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))

[cottrera]

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

[Anonymous]

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