Unable to convert a column calculation to a measure

[monojchakrab]

Hiya All,

I have a table which has sales by pin codes, some of which are repeating...

I am trying to count the rows for the repeating PIN codes in the fact table.

When I am trying to insert a calculated column, it works like a song with the following code :

Repeat PINS = 
COUNTROWS(
    FILTER('Amazon YTD',
    EARLIER('Amazon YTD'[Shipping Postal Code])=
    'Amazon YTD'[Shipping Postal Code])
)

But when I am trying to write the same code as a measure, using iterator function like COUNTX, it returns an error :

Count of Pin codes = 
COUNTX(
'Amazon YTD',
FILTER('Amazon YTD',
EARLIER('Amazon YTD'[Shipping Postal Code])=
'Amazon YTD'[Shipping Postal Code]
)
)

And the error I am getting is as follows :

Am I missing something?

Any help appreciated.

[SpartaBI]

@monojchakrab first of all in countx your 2nd argument is a table and it needs to be an expression, so just regarding the error, you need to write:

Count of Pin codes = 
COUNTX(
    FILTER('Amazon YTD',
        EARLIER('Amazon YTD'[Shipping Postal Code])=
        'Amazon YTD'[Shipping Postal Code]
    ),
    'Amazon YTD'[Shipping Postal Code]
)

or:
COUNTROWS(
    FILTER('Amazon YTD',
        EARLIER('Amazon YTD'[Shipping Postal Code])=
        'Amazon YTD'[Shipping Postal Code]
    )
)

but I'm not sure it will give you your desired result, let me know

[monojchakrab]

Thanks for the quick revert @SpartaBI ...

With the 1st option, the error is as follows :

with Option 2 (which I actually had tried befre using COUNTX) the screen grab is as follows :

Both of them seem to failing at the line where the EARLIER function is being used

[SpartaBI]

@monojchakrab ye that's what I imagined, I don't think you can use the same approach. Can you share a sample data and just write what is the desired result (in a number) and I'll share with you the solution.

[monojchakrab]

Shipping City	Shipping Postal Code	Repeating PINS
NOIDA	201301	18
GAUTAM BUDH NAGAR	201301	18
DELHI	110092	10
COIMBATORE	641107	9
DEHRADUN	248001	9
GURGAON	122001	9
KOLKATA	700019	8
THRIKKAKKARA . ERNAKULAM	682021	7
PUNE	411008	7
MUMBAI	400053	7
GURGAON	122018	7
PUNE	411014	7
KALAMASSERY	682021	7
ERNAKULAM	682021	7
MUMBAI	400067	6
JABALPUR	482001	6
MUMBAI	400064	6
AMRITSAR	143001	6
CHENNAI	600117	6
MUMBAI	400076	6
DELHI	110018	6
HYDERABAD	500068	5
NELLORE	524002	5
BHOPAL	462016	5
VIJAYAWADA	520010	5
BANGALORE	560043	5
GURGAON	122002	5

I am attaching the table which I summarized from the main table, along with the calculated column.

So basically, with the measure, I want to see the same number for the [repeating PINS] as I have in the column.

Hope this works for you!

[SpartaBI]

@monojchakrab ye that will work. Just to make sure, for your sample data this is the result for that column right?

and this is the way you want to see it in the visual?

P.S. will appreciate your kudos :))

[monojchakrab]

Yes....But where is the code?

[SpartaBI]

@monojchakrab I haven't done it yet haha, just wanted to make sure what you want 🙂
You know you can just do a measure like AVERAGE('Amazon YTD'[Repeating PINS]) on that column and you will get what you want 🙂 Do you want also a measure without that column at all?

[monojchakrab]

@SpartaBI , It would be great if we could solve it thru' a measure as then I can use the measure in other places in the model as well.

[SpartaBI]

@monojchakrab ok, will be back to my pc in 30 minutes and will write the measure and also share here the file with you. 

P.S. please kudos my posts 😆😆

[SpartaBI]

@monojchakrab will PM you now

[SpartaBI · Accepted solution]

@monojchakrabhere is a link to a file with a solution example I made for this question: Unable to convert a column calculation to a measure 2022-06-30.pbix
let me know if this is what you meant

      

Showcase Report – Contoso By SpartaBI