Calculate aggregate columns while keeping external filters and avoiding circular dependencies

[Anonymous]

Hi, 

I want a report in my table which gives me the aggregate prospects and customers grouped by some specific fields like Activity clusters and Trade Channels. 

These are my DAX operations for a couple of calculated columns :

Total prospects by category =

VAR

category = complete_prospect_customer[City]

VAR

category2 = complete_prospect_customer[Trade name]

RETURN

CALCULATE(DISTINCTCOUNT([DUNS]), KEEPFILTERS(FILTER(ALL(complete_prospect_customer), ([City] = category && [Trade Name]=category2 && complete_prospect_customer[Indicator]="P"))))

Total customers by category =

VAR

category = complete_prospect_customer[City]

VAR

category2 = complete_prospect_customer[Trade Name]

RETURN

CALCULATE(DISTINCTCOUNT([DUNS]), KEEPFILTERS(FILTER(ALL(complete_prospect_customer), ([City] = category && [Trade Name]=category2 && complete_prospect_customer[Indicator]="C"))))

I get a circular dependency error while using this. Upon removing the 'Keepfilters', although I get the aggregate values correctly, I am unable to use make use of my external filters in the report. 

I tried different methods like GroupBy in Power Query. There again since a new table is created, I cannot make use of my existing filters in the filter pane in from the current table; thereby not serving my purpose. 

Please help with the issue. I need aggregate values for customers and prospects in a table grouped by city and trade name; but at the same time I want it to be synced with any other filters present in the filter pane of my report.

[v-frfei-msft]

Hi @Anonymous ,

Please try to use the formula as below.

Total customers by category =
CALCULATE (
    DISTINCTCOUNT ( [DUNS] ),
    FILTER (
        complete_prospect_customer,
        prospect_customer[City] = EARLIER ( prospect_customer[City] )
            && prospect_customer[Trade Name] = EARLIER ( prospect_customer[Trade Name] )
            && complete_prospect_customer[Indicator] = "C"
    )
)

If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hi @v-frfei-msft , 

Thank you for the response. I tried using the 'Earlier' function but it still doesn't work. 

I have tried a second approach by creating a new table using GroupBy. I still can't figure our how to use our existing filters from the previous table to filter values of the new table.

I have created a sample data file and a pbix for the task. But I am allowed to share the OneDrive link only with people from within my organization. Can I mail it to you or is there any other way to share the same ?

Thanks,

@Anonymous 

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Hi @amitchandak ,

I cannot share the OneDrive/Google Drive link with people outside my organization due to restrictions. Can I mail you the file or can you suggest any other way to share the pbix file with you ? 

Thanks,

@Anonymous 

[v-frfei-msft]

Hi @Anonymous ,

Copy your data to excel and paste it here directly.