CALCULATE with SELECTDISTINCT and FILTER on Existing Measure

[Anonymous]

Hi,

Showing my question/example in Excel to protect actual data, but I'm doing this work in BI.

I have the following table called Accounts in a dataset:

I also have a measure (Account ID DistinctCount) that gives me the DISTINCTCOUNT of Account ID's for firms:

Here is the DAX for my Account ID DistinctCount measure:   DISTINCTCOUNT(Accounts[Account ID]).

This measure is working as expected.

I'm now trying to write a measure2. For measure2, I need to calculate the total number of Firms that a) have at least 2 accounts, and b) the Values of said accounts exceed 1. Given the dataset above, I'd expect my result to be 1. (Firm 2 is the only firm with at least 2 accounts whose Values exceed 1).

I tried writing this CALCULATE in DAX as follows:

Measure = CALCULATE(

DISTINCTCOUNT(Accounts[Firm ID]),

FILTER(Accounts,Accounts[Account ID DistinctCount]>2),

FILTER(Accounts,Accounts[Value]>1))

PowerBI is approving my syntax, but the result I yield when I put this measure into a table is (null). Where am I going wrong? Thank you!

[Greg_Deckler]

@Anonymous - Here is one way:

Measure 2 = 
    VAR __Table = SUMMARIZE('Table (2)',[Firm ID],"Count",DISTINCTCOUNT('Table (2)'[Account ID]),"Value",SUM('Table (2)'[Value]))
RETURN
    COUNTX(FILTER(__Table,[Count]>2 && [Value] > 1),[Firm ID])

[Anonymous]

Thanks @Greg_Deckler. I'm a total newbie: what do I replace the underscores with? What is Table(2) representing?

I got this far, but stopped after several syntax flags:

= SUMMARIZE(DailyAUM,[company_name],"Count",DISTINCTCOUNT(DailyAUM[account_id]),"Value",SUM(DailyAUM[AUM]))

RETURN

COUNTX(FILTER(

Also, if it helps, I also tried putting a DISTINCTCOUNT inside of my FILTER from my original attempt, and got this:

SpecialFirmCount = CALCULATE(

DISTINCTCOUNT(Table[Firm ID]),

FILTER(Table,DISTINCTCOUNT(Table[Account ID])>99),

FILTER(Table,Table[Value]>1))

This got me a value, but higher than what I was expecting.

[Anonymous]

Quick update @Greg_Deckler 

I think I got the syntax down, but the result yielded was (null). Here's the query with actual referents:

SpecialFirmCount =

VAR Field1 =

SUMMARIZE(DailyAUM,DailyAUM[company_name],"Count",DISTINCTCOUNT(DailyAUM[account_id]),"Value",SUM(DailyAUM[AUM]))

RETURN

COUNTX(FILTER(DailyAUM,[DistinctCountAccountID]>99 && [AUM]>1),[company_name])

The "Field1" next to VAR, I just made up; not sure what that's referring to. 

Another query I tried that failed:

SpecialFirmCount = CALCULATE(

DISTINCTCOUNT(DailyAUM[company_name]),

FILTER(DailyAUM,DailyAUM[DistinctCountAccountID])>99,

FILTER(DailyAUM,DailyAUM[AUM]>1))

[Anonymous]

One more update for this evening. I've literally been at this for 12 hours straight.

I created a measure that gets very close. The DAX behind the measure is as follows:

CountAcctsValueGrtrThn1 = CALCULATE(

DISTINCTCOUNT(DailyAUM[account_id]),DailyAUM[AUM]>1)

This gives me the count of account IDs whose value is greater than 1.

I then created a basic table visual and added Firm as a single column. Then, I dragged my measure to be a filter on the table, and said, "CountAcctsValueGrtrThn1 is greater than 99" --> so, Show me Firms that have more than 99 account_id's with Values greater than 1.

The resulting table shows me the expected list:

Firm

Firm 1

Firm 2

Firm 3

Firm 4

Firm 5

But my goal is to show the number 5, not the names of the 5 firms. Since the field is a text string, I go to change it to Count (Distinct). But as soon as I do, my filter on measure CountAcctsValueGrtrThn1 goes away.

Table, filtered on my measure, correctly showing 5 results:

Table, measure filter removed, after hitting Count (Distinct)

I expected this value to show 5. But the value it shows, 59, is the total count of all Firms ("company_name"'s). It's almost as if I can't filter using multiple Count Distinct's?

Anyway, any help is appreciated. Thanks again!

[v-kelly-msft]

Hi  @Anonymous ,

You need a measure rather than just using count (distinctcount) in the field,could you pls upload your .pbix file(remove the confidentail information) to onedrive business and share the link with us?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft , thanks for your response.

What's the best way to mask PII before uploading the PBIX? If you have a link to instructions, I can take a look. Thanks!

[v-kelly-msft]

Hi @Anonymous ,

Just remove the confidential information or you could make a simple sample such as dragging some data from your file,then upload it to onedrive business and share the link with us.

Hope this make sense.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

I had a colleague assist me, but have now run into a separate issue.

The DAX that solved my problem was:

Measure - Firms with >100 accounts with >$1 =

var summarized_table = SUMMARIZECOLUMNS('Table'[company name],"accounts over $1",CALCULATE(COUNT('Table'[Account ID]),'Table'[AUM]>1))

return COUNTX(FILTER(summarized_table,[accounts over $1]>=100),'Table'[company name])

This Measure gives me the correct count.

However, anytime I try to add a filter on top of a visual using this measure, the visual breaks.

With filter:

Without filter:

Any idea the best way to prevent a Measure visual from breaking when applying filters?

[v-kelly-msft]

Hi  @Anonymous ,

Not quite sure without your .pbix file,try to use "summarize" in stead of "summarizecolumns"..

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!