count rows and filter

[Anonymous]

Hi Everyone, 

I have a date table and a table1 below , in my measure, I want to show # of wins of the largest year when the slicer is not selected, that I used the measure below: 

FY	Region	inbound/outbound	industry	stage	US_fee
FY 2018	Great Lakes	Outbound	Financial Services	Win	18000
FY 2018	Southeast	Outbound	Financial Services	Win	150000
FY 2017	Northeast	Outbound	Business Professional Services	Win	141000
FY 2020	Great Lakes	Inbound	Industrial Products	Withdrawal
FY 2020	Northeast	Outbound	Government	Withdrawal
FY 2020	Great Lakes	Inbound	Business Professional Services	Withdrawal
FY 2016	Great Lakes	Inbound	Other	Withdrawal
FY 2019	Great Lakes	Outbound	Manufacturing
FY 2019	Southeast	Inbound	Technology	Loss

win count = 
var __win = 
CALCULATE(COUNTROWS('Table1'),
ALL(FY[FY]),
'Table1',
    'Table1'[Stage]="Win")
return 
IF(SELECTEDVALUE(FY[FY],"FY 9000")<=MAX(FY[FY])
    ,CALCULATE(__win),
    CALCULATE(COUNTROWS(Table1),
                FILTER(ALL(FY[FY]),MAX(Table1[FY])=FY[FY]),
                Table1[stage]="Win"
    ))

 However it's not giving me the right answer... can Anyone help me with it?

Thanks!!!! 

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

With your measure, It works well while the slicer is not selected because there is no win in the largest year 2020. The result is as follows.

The corresponding value of measure for FY2016, FY2020 is 0, 0.

About your reply, maybe there is something wrong in the sample data. Whether the value of stage in FY2020 and FY2016 would be ‘Win’. If so, the result is as below.

The corresponding value of measure for FY2016, FY2017, FY2018, FY2019, FY2020 is 1, 1 , 2, 0, 3.

If I misunderstand your thought, please show me your sample data and expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft , 

Here's the data I have :

FY	Region	inbound/outbound	industry	stage	US_fee
FY 2020	Great Lakes	Outbound	Technology	Win	20000
FY 2020	Northeast	Outbound		Withdrawal
FY 2019	Great Lakes	Inbound		Withdrawal
FY 2019	Great Lakes	Outbound	Manufacturing
FY 2019	West	Outbound	Other	Win
FY 2019	Southeast	Outbound	Technology	Win
FY 2019	Great Lakes	Outbound	Private Equity	Loss
FY 2019	Great Lakes	Outbound	Industrial Products	Withdrawal
FY 2018	Northeast	Outbound	Industrial Products	Withdrawal	250000
FY 2018	Central	Outbound	Industrial Products	Win	76000
FY 2018	Central	Outbound	Business Professional Services	Win	21000
FY 2018	Northeast	Outbound	Nonprofit	Win	101750
FY 2017	Great Lakes	Outbound	Technology	Win	10000
FY 2017	Southeast	Outbound	Business Professional Services	Win	9500
FY 2017	West	Outbound		Loss	650000
FY 2017	Northeast	Outbound	Business Professional Services	Win	120000
FY 2017	Southeast	Outbound	Industrial Products	Win	99000
FY 2016	West	Outbound	Industrial Products	Win	124500
FY 2016	West	Outbound	Consumer Products	Win	46000
	Central	Inbound
	Northeast	US to US	Financial Institutions

when I calculated the win count, yes, it's giving me the correct number. However when I want to group it by Industry , it's not calculated right, see the screenshot below. but when there's slicer selected, the win count by industry is correct. 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You can create a measure as follows.

win count = 
var  m = MAX('Table'[FY])  
return
IF (
    ISFILTERED ( 'FY'[FY] ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLSELECTED ( 'Table' ),
        'Table'[Stage] = "Win"
    ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[FY] = m
        ),
        'Table'[Stage] = "Win"
    )
)

Result:


Best Regards
Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Not entirely sure I understand what you are going for. HASONEVALUE could be used to test whether something has been selected in a slicer. You could also COUNT or COUNTROWS for your column and compare that with the count of ALL that column or table. If you want the largest year, use MAX or MAXX? Depends on what you mean by largest.

[Anonymous]

Hi @Greg_Deckler , 

thanks for responding. the largest year would be the largest year of the row. I want to achieve something like this: if the slicer is selected, it returns the result to whatever the year selected (say if I select FY 2016, it returns 1 and if select FY 2020, it returns 3); otherwise it returns the largest year of the row (FY 2020, and the row count is 3).  that's why I used selected value to try to achieve this. However, I don't know what I did wrong that its' not retuning the correct result.