Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Constant Column for Table Based on Measure

Hello! I am trying to create a table below in Direct Query mode and am struggling with the last step. I am trying to count the number of items in a table based on status and want to have another colu...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    you mean with measure? you can try to create with two measures with such code:

        Count1 = CALCULATE( COUNTROWS( TableName ) ),

        Count2 = CALCULATE( COUNTROWS( TableName ), TableName[Status] = "A" )
     
    try to plot a Table Visual, with Status column and the two measures.
FreemanZ's avatar
FreemanZ
Super User
3 years ago

aha, then try to create a new table with the code below:

 
Table =
ADDCOLUMNS(
    VALUES( TableName[Status] ),
    "Count1",
    CALCULATE( COUNTROWS( TableName ) ),
    "Count2"
    CALCULATE( COUNTROWS( TableName ), TableName[Status] = "A" )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks!! This worked. Is there a way to acheive this without the use of tables?

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    you mean with measure? you can try to create with two measures with such code:

        Count1 = CALCULATE( COUNTROWS( TableName ) ),

        Count2 = CALCULATE( COUNTROWS( TableName ), TableName[Status] = "A" )
     
    try to plot a Table Visual, with Status column and the two measures.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks! This measure worked. It wasn't working on mine as I just realised there's another table filtering the contents.