Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Dynamic Line and Column chart from bipatterns.com

Learn how to create a Line and Column chart that is dynamically created via slicers.  Go to bipatterns.com for a guide.

 

Dynamic Line and Column Example.png

 

 

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

If you're in PowerPivot: you can create the table in excel and import it into Powerpivot. 
Add to DataModel.png

 

PowerBI: Select "Enter Data" and type or paste your measure names into the table.

 

PowerBI Enter Data.png

SSAS (any version before 2016): You can paste data from the clipboard, similar to PowerPivot.

 

The remaining option would be to add a simple SQL table statement to the ETL that loads the cube.

 

If you have more details on what you're using/where your data is coming from, or any other questions let me know.

View solution in original post

Anonymous
Not applicable

I added it to the bottom of the post on my blog.  If you have any issues with opening it, please let me know.  I hope this helps, but let me know if you need anything else.  I'm working on building out my blog with a lot of techniques I've been using, and this has been just one of them.

 

Thanks

View solution in original post

Anonymous
Not applicable

@anithat

 

Or were you refferring to something like this:

 

Two Lines Vs. Column PowerBI.png

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.

 

Thanks,

Ryan Durkin

View solution in original post

8 REPLIES 8
anithat
Resolver II
Resolver II

Hi @Anonymous

 

Thanks for sharing the dynamic slicer based visualisation.

 

I have gone through the Bipatterns.com.

 

Could you please help me with the syntax/Dax to create new disconnected table in this case

 setp2:

Create the disconnected tables, highlighted in red above.  These tables will consist of two columns, Position and Variable (Column or Line Variable for example).  The position column serves as a row index, while the Variable column will contain the names of the measures you want to use dynamically.

Anonymous
Not applicable

If you're in PowerPivot: you can create the table in excel and import it into Powerpivot. 
Add to DataModel.png

 

PowerBI: Select "Enter Data" and type or paste your measure names into the table.

 

PowerBI Enter Data.png

SSAS (any version before 2016): You can paste data from the clipboard, similar to PowerPivot.

 

The remaining option would be to add a simple SQL table statement to the ETL that loads the cube.

 

If you have more details on what you're using/where your data is coming from, or any other questions let me know.

Any chance can we get the PowerBI file and PowerPivot model.

Anonymous
Not applicable

I added it to the bottom of the post on my blog.  If you have any issues with opening it, please let me know.  I hope this helps, but let me know if you need anything else.  I'm working on building out my blog with a lot of techniques I've been using, and this has been just one of them.

 

Thanks

Hi @Anonymous

 

I have been using the given BI pattern and it was very useful.

 

Any chance that we can apply this to Line and clustered column chart. What I mean is to choose multiple measures on Line displaying multiple line on the chart each line for each measure and similar case with Multiple measures on column.

 

Please advise. Thank you!

Anonymous
Not applicable

@anithat

 

Or were you refferring to something like this:

 

Two Lines Vs. Column PowerBI.png

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.

 

Thanks,

Ryan Durkin

Hi @Anonymous

 

Yes, it is similar to this. Multiple bars and multiple lines chart.

 

It looks like you have taken two variables into one single variable and mapped it to the line.

 

Similar logic should work for columns as well which I will try.

 

Could you please share the pbix for the latest chart.

 

Also, What I am trying to achieve is little different from this.

 

I have two Line and column measures as below

 

Column:

Sales TY

Sales LY

 

Line:

Units TY

Units LY

 

Instead of chiclet slicer, I have taken regular slicer on pbi. Enabled the multiselect option. This allows me to select both the values from column and line.

 

Selecting sales TY & Sales LY should display two bars on chart with diff colors.

Selecting Units TY & Units LY should display two lines on Chart with diff colors.

 

In your case, to get this you are trying to map Units TY & Units LY to one variable and using which restricts the selection.

 

I want this to be dynamic, user should be able to chose either one measure at a time on column and line or two measures each at a time

Thank you!

 

Anonymous
Not applicable

@anithat

 

Are you looking for something like this?

 

View Report Here

 

 Measure Vs Measure.png

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. Go To bipatterns.com for more techniques and user guides.

 

Thanks,

Ryan Durkin

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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