DAX - VAR Measure help

[irenelitw629]

Hi all,

I want to create a calculate measurement with VAR. Whenever account and BU workflow filters are selected, the measure will display corresponding closed amount.

Issue: when BU workflow filter is not selected, I expect the value return with total value or be blank. Right now it is not displaying correctly.

Example: in attached file, measure is showing $2m when workflow filter is not selected. it should show as $3.5m or blank..

Can anyone check what is wrong with my formula:

Measure = VAR _acct = MAXX((Sheet1),Sheet1[Account Name]&'Sheet1'[BU Workflow FY22])

Return

CALCULATE(SUM(Sheet1[USD Closed])

,filter(ALL(Sheet1),

Sheet1[Account Name]&'Sheet1'[BU Workflow FY22]= _acct))

Thanks!!

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please try the below measure.

Measure: =
VAR _acct =
    MAXX ( Sheet1, Sheet1[Account Name] & 'Sheet1'[BU Workflow FY22] )
RETURN
    IF (
        ISFILTERED ( Sheet1[BU Workflow FY22] ),
        CALCULATE (
            SUM ( Sheet1[USD Closed] ),
            FILTER (
                ALL ( Sheet1 ),
                Sheet1[Account Name] & 'Sheet1'[BU Workflow FY22] = _acct
            )
        )
    )

[irenelitw629]

Hi Kim,

Thanks! but it seems not working

Can you check the file? maybe it will help you to understnad the issue. https://drive.google.com/file/d/1HEpm7gaq7O4t_4bBNPE5yYFRXIo-T_jh/view?usp=sharing

Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the attached file.

If no-select, then it is blank.

[irenelitw629]

Amazing. It works!! Thank you so much Kim!!

[irenelitw629]

Link to file: https://drive.google.com/file/d/1HEpm7gaq7O4t_4bBNPE5yYFRXIo-T_jh/view?usp=sharing

[amitchandak]

@irenelitw629 , Try if one of two can help

Measure = VAR _acct = MAXX((Sheet1),Sheet1[Account Name]&'Sheet1'[BU Workflow FY22])
Return
CALCULATE(SUM(Sheet1[USD Closed])
,filter(ALL(Sheet1), if(isfiltered([BU Workflow FY22]) ,
Sheet1[Account Name]&'Sheet1'[BU Workflow FY22]= _acct) , true()))

or

Measure = VAR _acct = MAXX((Sheet1),Sheet1[Account Name]&'Sheet1'[BU Workflow FY22])
Return
CALCULATE(SUM(Sheet1[USD Closed])
,filter(ALL(Sheet1), if(isfiltered([BU Workflow FY22]) ,
Sheet1[Account Name]&'Sheet1'[BU Workflow FY22]= _acct) , false()))

yet to check the file

[irenelitw629]

Hi Amit,

Thanks! but it seems not working - can you try the file?

https://drive.google.com/file/d/1HEpm7gaq7O4t_4bBNPE5yYFRXIo-T_jh/view?usp=sharing