DAX Measure

[Anonymous]

Looking for a DAX measure:

Looking for measure that would filter according to a column:
Example:
Measure 1 = Calculate(Sum('Trade'[Value]), filter='Reporter Country')
Measure 2 = Calculate(Sum('Trade'[Value]), filter='Partner Country')

Reporter Country	Partner Country	Value
China	Japan	56

So measure 1 should show values  for Reporter Countries that are selected and Measure 2 should give values for Partner Countries

[VahidDM · Accepted solution]

Hi @Anonymous 

You need to use the selectedvalue DAX formula:

Mreasure 1 =
VAR _A =
    SELECTEDVALUE ( 'Trade'['Reporter Country'] )
RETURN
    CALCULATE (
        SUM ( 'Trade'[Value] ),
        FILTER ( ALL ( 'Trade' ), 'Trade'['Reporter Country'] = _A )
    )

Mreasure 2 =
VAR _A =
    SELECTEDVALUE ( 'Trade'['Partner Country'] )
RETURN
    CALCULATE (
        SUM ( 'Trade'[Value] ),
        FILTER ( ALL ( 'Trade' ), 'Trade'['Partner Country'] = _A )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi @Anonymous ,

You will need to create two indempendent slicer table:

Reporter = distinct('Trade'[Reporter Country])

Partner = distinct('Trade'[Partner Country])

Then create measures like below:

measure 1 =
CALCULATE (
    SUM ( 'Trade'[Value] ),
    FILTER (
        ALLSELECTED ( 'Trade' ),
        'Trade'[Reporter Country] = SELECTEDVALUE ( 'Reporter'[Reporter Country] )
    )
)

measure 2 =
CALCULATE (
    SUM ( 'Trade'[Value] ),
    FILTER (
        ALLSELECTED ( 'Trade' ),
        'Trade'[Partner Country] = SELECTEDVALUE ( 'Partner'[Partner Country] )
    )
)

Best Regards,

Jay

[Anonymous]

Can we add multiple filter?

[Anonymous]

Hi @Anonymous ,

Do you mean sum the sales for multiple countries selected in the slicer?

If so you just need to replace selectedvalue() function to values() function.

https://docs.microsoft.com/en-us/dax/selectedvalue-function 

https://docs.microsoft.com/en-us/dax/values-function-dax 

Please show the expected result if I misunderstood your meaning.

Best Regards,

Jay

[Anonymous]

Nope it should filter country column, Year colum, Product column

[Anonymous · Accepted solution]

Hi @Anonymous ,

measure =
CALCULATE (
SUM ( 'Trade'[Value] ),
FILTER (
ALLSELECTED ( 'Trade' ),
'Trade'[Reporter Country] = SELECTEDVALUE ( 'Reporter'[Reporter Country] )
&& 'Trade'[year] = SELECTEDVALUE ( 'year'[year] )
&& 'Trade'[product] = SELECTEDVALUE ( 'product'[product] )
)
)

Or

measure =
CALCULATE (
SUM ( 'Trade'[Value] ),
FILTER (
ALL ( 'Trade' ),
'Trade'[Reporter Country] = SELECTEDVALUE ( 'Trade'[Reporter Country] )
&& 'Trade'[year] = SELECTEDVALUE ( 'Trade'[year] )
&& 'Trade'[product] = SELECTEDVALUE ( 'Trade'[product] )
)
)

Best Regards,

Jay

[VahidDM · Accepted solution]

Hi @Anonymous 

You need to use the selectedvalue DAX formula:

Mreasure 1 =
VAR _A =
    SELECTEDVALUE ( 'Trade'['Reporter Country'] )
RETURN
    CALCULATE (
        SUM ( 'Trade'[Value] ),
        FILTER ( ALL ( 'Trade' ), 'Trade'['Reporter Country'] = _A )
    )

Mreasure 2 =
VAR _A =
    SELECTEDVALUE ( 'Trade'['Partner Country'] )
RETURN
    CALCULATE (
        SUM ( 'Trade'[Value] ),
        FILTER ( ALL ( 'Trade' ), 'Trade'['Partner Country'] = _A )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/