MEasure crashes everytime no matter how tiny the dataset

[Anonymous]

Hi there,

sorry for this beeing complicated but I try to explain my problem in detail

I wrote a measure to find out how many Distinct values are found in column in temporary table that is created out of another key column in the same table.

That temporary table is built up by using a FILTER() Function. My measure looks like this:

distinct Counter = CALCULATE (DISTINCTCOUNT (TABLE_X[ColumnToCountDistinctValues]),FILTER (TABLE_X,TABLE_X[ColumnWithDuplikateValues] = EARLIER (TABLE_X[ColumnWithDuplikateValues])))

But it crashes everytime. The "We are working on it" Dialigue never stops and mostly after 15 min I hardreset PowerBI.

Can anybody help me?

Funny case: this measure (without using DISTINCTCOUNT) works fine and fast, but it does not help me because it dies not count how often the two values are different

distinct Counter = CALCULATE (COUNT (TABLE_X[ColumnToCountDistinctValues]),FILTER (TABLE_X,TABLE_X[ColumnWithDuplikateValues] = EARLIER (TABLE_X[ColumnWithDuplikateValues])))

The Goal is: The Column [ColumnWithDuplikateValues] has some duplikate values because of another column (here to be called [anotherColumn] which has almost only distinct values) that is causing this (it´s a SQL-JOIN matter, but it is fine like this). There is now another Column, the [ColumnToCountDistinctValues], which is not necessary different than [ColumnWithDuplikateValues], but it can be. 

Often both Columns have the same vlaue, only the [anotherColumn] has different ones. 

My Problem is: I want to map the content of [ColumnToCountDistinctValues] using [ColumnWithDuplikateValues] as the connection between the tables to another table using LOOKUPVALUE, but this is not possible because it says there are duplicate values delivered.

So in some occasions [ColumnToCountDistinctValues] is not the same than [ColumnWithDuplikateValues].

In order to find out how many times the values are different in both columns I need the above mentioned measure.

[Greg_Deckler]

@Anonymous Try this:

distinct Counter = 
  VAR __Column = MAX('TABLE_X'[ColumnWithDuplikateValues])
  VAR __Table = SELECTCOLUMNS(FILTER('TABLE_X', [ColumnWithDuplikateValues] = __Column),"__Column",[ColumnWithDistinctValues])
  VAR __Result = COUNTROWS(DISTINCT(__Table, [__Column]))
RETURN
  __Result

[Anonymous · Accepted solution]

Hi @Greg_Deckler 

me again, I tried a bit and now it works with this here:

distinct Counter = 
  VAR __Column = [ColumnWithDuplikateValues])
  VAR __Table = SELECTCOLUMNS(FILTER('TABLE_X', [ColumnWithDuplikateValues] = __Column),"__Column",[ColumnWithDistinctValues])
  VAR __Result = COUNTROWS(DISTINCT(__Table))
RETURN
  __Result

Just delete the MAX in the first row and the [__Column] in the COUNTROWS(DISTINCT( Function

and now it works fine 🙂

thank you very much!

[Anonymous]

Hi @Greg_Deckler 

thanks for your suggestion.

The problem is I get an error when I use your code for a calculated column. the error says:

Too many argemtns passed --> problem is this here: 

VAR __Result = COUNTROWS(DISTINCT(__Table, [__Column]))

I can only pass the __Table to this function. But now I get value cound 1 for everything so I think it does not work