Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
@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
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
@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.
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!
@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 :))
Yes....But where is the code?
@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?
@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.
@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 😆😆
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |