cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oklande Frequent Visitor
Frequent Visitor

Distinct values in in each category bin, based on countrows

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 

PowerBI DAX.png

 

Any help or guidance is greatly appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Distinct values in in each category bin, based on countrows

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

1 REPLY 1
Highlighted

Re: Distinct values in in each category bin, based on countrows

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