Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
monojchakrab
Resolver III
Resolver III

Unable to convert a column calculation to a measure

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 :

 

monojchakrab_0-1656578446828.png

Am I missing something?

Any help appreciated.

 

1 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


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

11 REPLIES 11
SpartaBI
Community Champion
Community Champion

@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 :

monojchakrab_0-1656579946281.png

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

monojchakrab_1-1656580046719.png

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 CityShipping Postal CodeRepeating PINS
NOIDA20130118
GAUTAM BUDH NAGAR20130118
DELHI11009210
COIMBATORE6411079
DEHRADUN2480019
GURGAON1220019
KOLKATA7000198
THRIKKAKKARA . ERNAKULAM6820217
PUNE4110087
MUMBAI4000537
GURGAON1220187
PUNE4110147
KALAMASSERY6820217
ERNAKULAM6820217
MUMBAI4000676
JABALPUR4820016
MUMBAI4000646
AMRITSAR1430016
CHENNAI6001176
MUMBAI4000766
DELHI1100186
HYDERABAD5000685
NELLORE5240025
BHOPAL4620165
VIJAYAWADA5200105
BANGALORE5600435
GURGAON1220025

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?

SpartaBI_0-1656583679406.png

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

SpartaBI_1-1656583775258.png

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 😆😆

@monojchakrab will PM you now

@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


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.