Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |