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
morther
Helper I
Helper I

Finding the difference between two columns

This CPC column is filtering as I need it to, and there are no errors.

 

CPC = CALCULATE( COUNT(ImportCubicPM[CPC]),
FILTER(ALL(ImportCubicPM[CPC]),
ImportCubicPM[CPC] = "MP-MONTHLYPM-PM-"))

 

I have a Measure called (ImportCubicPM[TotMP] which is a static count of 7 units.

 

I am trying to calculate the difference between these 2 columns, but am receiving an error.

 

CPC = CALCULATE (ImportCubicPM[TotMP] -
(COUNT(ImportCubicPM[CPC]),
FILTER(ALL(ImportCubicPM[CPC]),
ImportCubicPM[CPC] = "MP-MONTHLYPM-PM-")))

 

The error is "Operator or expression '()' is not supported in this context.  

 

Thanks,

Mike

8 REPLIES 8
morther
Helper I
Helper I

This works when I subtract 2 columns

CPC = CALCULATE (Drive_Station[TotMP] - 
(COUNT(ImportCubicPM[CPC])))

This works when I Filter a column

CPC = CALCULATE(COUNT(ImportCubicPM[CPC]),
      FILTER(ALL(ImportCubicPM[CPC]),
        ImportCubicPM[CPC] = "MP-MONTHLYPM-PM-"))

 When I try to combine these 2 formulas, I receive an error "Operator or expression '()' is not supported in this context"

CPC = CALCULATE (Drive_Station[TotMP] - 
     (COUNT(ImportCubicPM[CPC]),
      FILTER(ALL(ImportCubicPM[CPC]),
        ImportCubicPM[CPC] = "MP-MONTHLYPM-PM-"))
TomMartens
Super User
Super User

Hey,

 

here you can find  my version of your pbix file

 

Your DAX statement for your calculated column just creates values for the filtered rows leaving the other rows empty. In the final calculation this leads to the error message because these empty cells are interpreted as an empty string. For this reason I rewrote this in the final DAX statement - the measure [CPC_COUNT_DELTA]

There is a second measure CPC_COUNT_CHECK this measure just counts, there is an issue for the "Current Month: 2017 April". My Measure counts 7 your Calculated column counts 9, please check.

 

I used the name CPC_COUNT to create a calculated column using the DAX you provided.

 

I do not want to be rude, but please be a little more precise the next you are asking for help

  • You can't use a name for a calculated column that already exists in a table
  • The same for the code for the measure you provided
  • The name of the measure with the static value of 7, is called TotalMP

All this adds to some extra time trying to solve your issue

 

Here is really greast post by @Greg_Deckler what ideally should be contained in any question, but not all that is mentioned has to be provided, but please read this.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks Tom.  

When I add CPC_COUNT_DELTA to the "No MP PMs" card on the "Cubic PMs" tab and then select the month August, you can see that it is calculating properly.  You can see in the table "Devices with No PMs" that there are 2 MPs in the list.

MP49999 and MP36301.

 

If you select the month of July the results show 0, but in the "Devices with no PMs" table you can see that there is 1 MP device.

MP49999

 

I see that you were doing your calculations from the ImportCubicPM table  I was creating the column in the Drive_Station table.  I use this table because it has a list of all of the devices, but the ImportCubicPM table only has devices where a PM was created for the month.   There is also a card with a big, red number that shows "Total Devices No PMs"  This is giving me the correct total of all devices in a month with no PMs. 

 

What I am trying to get at is break down the devices by filtering MP devices, SAV devices, and TVM Devices.

 

I hope this makes sense.

 

Thanks, Mike

 

 

 

TomMartens
Super User
Super User

Hey,

 

please share a pbix with simplified data, by providing a link to the file on onedrive or dropbox.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

I think that should be shared for you.

 

I did change the name from CPC to Monthly MP, but it is the same code

jbocachica
Resolver II
Resolver II

But you are trying to get the difference between a Column and a Measure?, is CPC a Column or a Measure?, if it's a Column, you should create a Measure to achive that.

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co

CPC is a column, but Drive_Station[TotMP] is a measure.

 

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.