How to get data using slicer in dax calculartion

[Anonymous]

Hello All,

I have a table where I have data like below.

Country	Sales
India	45000
India	45000
India	20000
UK	17000
UK	18000
Uk	19000
Canada	2000
Canada	25000

Now as per my requirement I created a table inside power bi with values EMEA,West and APAC

Now my requirement is I have took a slicer with the table values APAC ,EMEA and West

If I select slicer value as APAC then it has to show India sales 

and If I select EMEA it has to show UK sales

and If I select West it has to show Canada sales

Could any one help what is the dax function used here 

[Ashish_Mathur]

Hi,

Create a 2 column table with column for Country and Region.  In your dataset (Table1), write this calculated column formula

Region = lookupvalue('table2'[region],'table2'[Country],'table1'[Country])

To your visual, drag Region from Table1.  Write this measure

Revenue = sum(Table1[Sales])

Hope this helps. 

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I create a sample.

Table:

Slicer Table:

Here's my solution, create a measure.

Check =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "APAC"
        && SELECTEDVALUE ( 'Table'[Country] ) = "India", 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "EMEA"
        && SELECTEDVALUE ( 'Table'[Country] ) = "UK", 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "West"
        && SELECTEDVALUE ( 'Table'[Country] ) = "Canada", 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = BLANK (), 1,
    0
)

Put the measure in the visual filter and let its value to 1.

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft ,

Thanks for your response.

I need to add another value (Overall) in slicer table which should show all countires data when I select overall.

So If I select Overall then I need to show Inda,Canada and UK data

Could you please provide the dax for this

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, modify the formula like this:

Check =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "APAC"
        && SELECTEDVALUE ( 'Table'[Country] ) = "India", 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "EMEA"
        && SELECTEDVALUE ( 'Table'[Country] ) = "UK", 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "West"
        && SELECTEDVALUE ( 'Table'[Country] ) = "Canada", 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = BLANK (), 1,
    SELECTEDVALUE ( 'Slicer Table'[Slicer] ) = "Overall", 1,
    0
)

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@Anonymous , Please create a new column based on country and use that in slicer

Region =

Switch( [Country],

"India", "APAC",

"UK", "EMEA",

"Canada", "West")

[Anonymous]

Hi @amitchandak ,

Should I call slicer values directly as you mentioned above.

Could you please let me know

Here the APAC ,EMEA and West values are coming from slicer