Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

MEasure crashes everytime no matter how tiny the dataset

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.
 
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.