Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there,
I have a column which contains decimals, dates and text. I want to sum it when the data type is decimal, and ideally just show the text or date values unaggregated - but if this is not possible, to just ignore non-decimal values.
Is this possible with some sort of IFERROR? I've tried but am not getting the right syntax.
My column name is [value] and my table name is 'Exception Details'
Hello,
did you try something like this?
SumOfDecimals =
SUMX(
'Exception Details',
IF(
ISNUMBER('Exception Details'[value]),
'Exception Details'[value],
0
)
)
Thanks @Gabry - the measure no longer errors, however it just shows 0 for everything, even when I filter the value column so I know it's just decimal numbers. I think the issue could be because the column type is text, the IF(ISNUMBER is not picking up the numbers.
I understand, can't you set up the data type to any in power query? Just wondering. If not maybe you can try convert it
SumOfDecimals =
SUMX(
'Exception Details',
IF(
ISERROR(CONVERT('Exception Details'[value], DOUBLE)),
0,
CONVERT('Exception Details'[value], DOUBLE)
)
)
I found a data type column, so i've actually used M Query conditional columns to create separate columns for the different data types. Then my measures can work independently. Thanks!
User | Count |
---|---|
77 | |
76 | |
44 | |
31 | |
26 |
User | Count |
---|---|
97 | |
89 | |
52 | |
48 | |
46 |