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
CS
Frequent Visitor

Grand Total Row Count When Using Measures, IF, and FILTER

I want to get the sectors that have increased their hours this month. 

 

=IF( [Total Hours] > [Hours LM],
       IF( 
            HASONEFILTER( 'Table B-7'[Super Sector] ),
            COUNTROWS( 'Table B-7' ),
            ???
        )
)

There are 19 sectors in total. Nine of the sectors have increased their hours. While I can identify the 9 sectors that have increased their hours, I cannot get a correct grand total of 9. I have tried a number of different formulas, but I cannot get the correct total.

 

How can I get the correct row count for the grand total?

1 ACCEPTED SOLUTION

@CS

 

Give this a shot

 

????? =
SUMX (
    FILTER ( ALLSELECTED ( 'Table B-7'[Super Sector] ), [Total Hours] > [Hours LM] ),
    CALCULATE ( COUNTROWS ( 'Table B-7' ) )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

HI @CS

 

I think Something along the following lines.

 

?????=
SUMX (
    ALLSELECTED ( 'Table B-7'[Super Sector] ),
    CALCULATE ( COUNTROWS ( 'Table B-7' ) )
)

Regards
Zubair

Please try my custom visuals

Thank you, @Zubair_Muhammad.

I just tried it and I am still getting 19, the total of all the super sector rows. 

 

@CS

 

Give this a shot

 

????? =
SUMX (
    FILTER ( ALLSELECTED ( 'Table B-7'[Super Sector] ), [Total Hours] > [Hours LM] ),
    CALCULATE ( COUNTROWS ( 'Table B-7' ) )
)

Regards
Zubair

Please try my custom visuals

That worked, thank you. 

 

I thought measures could not be used in FILTER. I tried using measures in FILTER to no avail. When I read up on it, I found something that said measures cannot be used in FILTER. But look at this, they can. 

 

Thank you again.

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.