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
SophRow
Resolver II
Resolver II

Create new summary table

Hi,

 

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

 

SophRow_0-1669242944452.png

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

SophRow_1-1669243123109.png

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

 

Thanks,

Soph

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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:
FreemanZ_0-1669254910937.png

 

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

to your Table visual, drag Site and this measure

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
FreemanZ
Super User
Super User

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:
FreemanZ_0-1669254910937.png

 

Brilliant - thank you 🙂

 

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

djurecicK2
Super User
Super User

@SophRow 

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.

djurecicK2
Super User
Super User

Hi @SophRow ,

 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.

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

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

 

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.