topic Re: Using two Datasets in power BI in Developer

Using two Datasets in power BI

udayadeven — Mon, 04 Sep 2023 15:26:12 GMT

Hi Experts ,

I have two datasets having same fields from two lists .. first list having current month data and the second one is having historical data ..

How can we switch the datasets in the same powerbi Report . In brief , i need to have a check box(or any otherway) to select the current or history dataset and change the report figures accordingly

pls your advice on this

rgds

Re: Using two Datasets in power BI

timahenning2 — Mon, 04 Sep 2023 16:52:54 GMT

Have you thought about combining (union) the datasets into one table? Your report and measures would point to one table. Using a slicer, filtered by a field like date, you could then display current or historical data.

Re: Using two Datasets in power BI

udayadeven — Thu, 07 Sep 2023 08:09:53 GMT

Hi Timahenning ,

Thanks for your advice ,.. yes , i am trying to use the UNION . but I a quick concern on this .. In my first dataset having new columns and Messures created thro PowerBI , Do i need to have the same things in second Dataset .. I tried to create but cannot use the same Messure name ..

pls advice ..

rgds

Re: Using two Datasets in power BI

timahenning2 — Thu, 07 Sep 2023 13:57:37 GMT

Ideally, your dataset transformations should be done before you are working in your Power BI model. Transform data as close to the data source as possible.
Roche’s Maxim of Data Transformation – BI Polar (ssbipolar.com)

Your original statement: "I have two datasets having same fields from two lists .. first list having current month data and the second one is having historical data "

If you are working with files, then you should use the Append functionality in Power Query to combine your datasets into one.

Working with a database as your data source? Create a database view to transform the data into one dataset (UNION) and then import into Power Query/Power BI.

Union requires the fields to be the "same" interface. You can find the definiton of "same" in the DB service help files and examples. It makes more sense when you see the examples.

Tim

Re: Using two Datasets in power BI

udayadeven — Fri, 08 Sep 2023 05:31:15 GMT

Hi timahenning ,

thanks for the advice .. I am using share Point Lists for this ..

rgds

Re: Using two Datasets in power BI

timahenning2 — Fri, 08 Sep 2023 14:04:11 GMT

AD, the concept is the same for SharePoint Lists.

1. Open Power Query and select the current SharePoint List table. Choose Append Queries As New from the PQ menu bar.

2. Select the tables like this to union the data in Power Query:

3. Your Power BI model should look something like this:

4. You can now view the current and historical data in your visualization by changing the way your filter the data.

Tim

Re: Using two Datasets in power BI

timahenning2 — Mon, 11 Sep 2023 13:39:51 GMT

If I have provided a solution, would you mind a selecting the thumbs up?

Thanks.

Re: Using two Datasets in power BI

udayadeven — Tue, 12 Sep 2023 09:30:37 GMT

Thanks timahenning2 for your advice .. I did it as you said .. its working as expected

rgds