Distinct values in in each category bin, based on countrows

[Anonymous]

Hi all, 

I am struggling to translate a calculated column into a measure - not sure if it's even possible (?).

I have a dataset, with two relevant columns: AccountNo, and InvoiceNo. 

I want to create a histogram using the bins in a secondary 'bins' table, where its value depend on how many distinct InvoiceNo fits into each 'bin' - based on the number of rows (countrows) there are for the given InvoiceNo in the data table. 

Here's the catch: it must be possible to change the result by slicing the AccountNo field (or any other column from the data table). 

I can produce the desired result using a calc. table and summarize (see downloadable sample). Essentially, I am trying to reproduce the calc. table into a measure - so it can interact with filters. 

Sample .pbix can be found here (OneDrive): https://1drv.ms/u/s!AkOYJVjp7_Dk3RS87Zb1yMe7-UJM

Details are in the picture below 

Any help or guidance is greatly appreciated. 

[avanderschilden · Accepted solution]

Hi Oklande,

Try this one;

Measure =
CALCULATE (
COUNTROWS (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Data, Data[InvoiceNo] ),
"Rows", CALCULATE ( COUNTROWS ( Data ), Data[InvoiceNo] = EARLIER ( Data[InvoiceNo] ) )
),
COUNTROWS (
FILTER (
CategoryBins,
[Rows] >= CategoryBins[From]
&& [Rows] <= CategoryBins[To]
)
) > 0
)
)
)

Let me know what you think about it.

Regards,

Adrian