cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## dynamically Data from Two table using slicer in card

Dear All,

I am very new in power BI therefore need a favour in dax, Please help me to create a Dax,
I have two data sets table 1 & table 2 as given below. The requirement is total sales USD 2000 from table 1, but when we use slicer for counry values should be pick up from table 2. i.e Canada  500, Russia 200 and Germany 300. Please help me to get this in a card in power BI.

 Table 1 Table 2 Country name Sales Country Sales USA 690 Canada 500 India 780 Russia 200 Canada 340 Germany 300 Russia 30 Germany 160 Total Sales 2000
1 ACCEPTED SOLUTION
Community Support

I created a measure and replaced the data from Table1 with the data for the Table2 country, as shown in the following image

Here is the DAX:

``````Dynamic Sales =
VAR SelectedCountry =
SELECTEDVALUE ( 'Table2'[Country], "All" )
RETURN
IF (
SelectedCountry = "All",
SUMX ( 'Table1', 'Table1'[Sales1] ),
CALCULATE ( SUM ( 'Table2'[Sales] ), 'Table2'[Country] = SelectedCountry )
)
``````

Then create a measure to calculate the totals for Table1

DAX:

``Dynamic Sales Total = SUMX('Table1','Table1'[Dynamic Sales])``

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

4 REPLIES 4
Community Support

I created a measure and replaced the data from Table1 with the data for the Table2 country, as shown in the following image

Here is the DAX:

``````Dynamic Sales =
VAR SelectedCountry =
SELECTEDVALUE ( 'Table2'[Country], "All" )
RETURN
IF (
SelectedCountry = "All",
SUMX ( 'Table1', 'Table1'[Sales1] ),
CALCULATE ( SUM ( 'Table2'[Sales] ), 'Table2'[Country] = SelectedCountry )
)
``````

Then create a measure to calculate the totals for Table1

DAX:

``Dynamic Sales Total = SUMX('Table1','Table1'[Dynamic Sales])``

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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

New Member

Dear Syk,

thanks for sharing your guidance, But the condition is some different, As per requirement. I need to show Total sales in a card from the  first table of country name, but when I use slicer , it should filter data  from second table like
country name canada is 500 as actual value, instead of canada (340 that is some overlap sales) because in first table some data is overlapping with some criteria.

Is it possible to show the data from other table using selectedvalue and switch like measures if yes, Please help

Super User

Possibly.. But your data would get messy and hard to understand very quickly. Is the requirement that if there is data in table 2 for a country, you no longer need the data from table 1? If there's no data in table 2, use the data from table 1?

Super User

If the data is the same, you should combine these into 1 table. Use the append query option to do this. If the "Total Sales" is an actual row in your data, remove it.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors