Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create new summary table

Hi,

 

I have a table of sites and various metrics, and I created a calculated column to define 'Query age'

 

What I need is a new table that summarise query age > 1 months for each site, i.e.,

Not sure how to do this as Query age is a calculated column!

 

Thanks,

Soph

Need Help
Show and Tell
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    try to create a new table with the code below:

    Table =
    ADDCOLUMNS(
        VALUES(TableName[Site]),
        ">1 month",
       CALCULATE(
            COUNTROWS(
                FILTER(
                    TableName,
                    TableName[Query age] <>"<1 month"
                )
            )
        )  
    )
     
    I tried and it works like this:

     

8 Replies

  • djurecic's avatar
    djurecic
    Super User
    3 years ago

    Hi Anonymous ,

     You could create another calculated colum that is true or false depending on if the query age is > 1 month. Then use that column to filter your count.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks! I've created that column - can you share the DAX for the count?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        (basically how i get to the second excel example in my first post)

         

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    try to create a new table with the code below:

    Table =
    ADDCOLUMNS(
        VALUES(TableName[Site]),
        ">1 month",
       CALCULATE(
            COUNTROWS(
                FILTER(
                    TableName,
                    TableName[Query age] <>"<1 month"
                )
            )
        )  
    )
     
    I tried and it works like this:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Brilliant - thank you 🙂

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        Anonymous  You are welcome. Is it qualified as a solution to your question?

  • djurecic's avatar
    djurecic
    Super User
    3 years ago

    Anonymous 

    Something like this- modify to fit your table and field names.

     

    CountMeasure= CALCULATE(COUNT(Tablename[Query age]), FILTER(Tablename, Tablename[New Calculated Column]= TRUE))

     

    Please consider accepting as solution if this has answered the question.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    to your Table visual, drag Site and this measure

    Measure1 = calculate(countrows(Data),Data[Query Age]<>"<1 month")

    Hope this helps.