Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Give this a shot
????? = SUMX ( FILTER ( ALLSELECTED ( 'Table B-7'[Super Sector] ), [Total Hours] > [Hours LM] ), CALCULATE ( COUNTROWS ( 'Table B-7' ) ) )
HI @CS
I think Something along the following lines.
?????= SUMX ( ALLSELECTED ( 'Table B-7'[Super Sector] ), CALCULATE ( COUNTROWS ( 'Table B-7' ) ) )
Thank you, @Zubair_Muhammad.
I just tried it and I am still getting 19, the total of all the super sector rows.
Give this a shot
????? = SUMX ( FILTER ( ALLSELECTED ( 'Table B-7'[Super Sector] ), [Total Hours] > [Hours LM] ), CALCULATE ( COUNTROWS ( 'Table B-7' ) ) )
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.