Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to calculate difference between two columns - related column and count column

I'm struggling with this.  I want to create a column that calculates the difference of (First RequiredShiftA) and (CountDuringShiftA) columns.   This started as two tables.  I created new columns i...
Calculations
DAX
Anonymous's avatar
Anonymous
Not applicable
7 years ago

The one column was a quick measure and doesn't show up in the data view.

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
7 years ago

Then don't use a "quick measure" and create a "calculated column" with the substract.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I've been trying.  I get a lot of errors.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I think this is what i'm trying to do - but have no idea - this DAX stuff is crazy.

       

      I basically want a count of all the during roles for shift b that gets subtracted from the # of required roles for shift b.  

       

      CALCULATE(

          COUNTA('Contact Export PowerBI'[During Section and Role]),

          'Contact Export PowerBI'[During Shift] IN { "B Shift" } - 'Contact Export PowerBI'[RequiredShiftB])

      • ibarrau's avatar
        ibarrau
        Icon for Super User rankSuper User
        7 years ago

        I don't think I'm following the whole idea. Try to use variables to avoid this kind of error. The formula you are showing looks like a measure. I think this code should help:

        VAR counting = COUNTA('Contact Export PowerBI'[During Section and Role]),

VAR in_shift_b = 
CALCULATE (
    countring , 
    'Contact Export PowerBI'[During Shift] = "B Shift" 
)
 VAR number_of_req_roles = COUNT('Contact Export PowerBI'[RequiredShiftB] )

RETURN
in_shift_b - number_of_req_roles

        Hope this helps,

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ibarrau's avatar
      ibarrau
      Icon for Super User rankSuper User
      7 years ago

      Can you send a picture of the data model? I think if we have only three tables with single directional relation we shouldn't be seeing this error.