cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rdurkin Member
Member

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

Accepted Solutions
rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

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.

rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

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

rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

@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

8 REPLIES 8
anithat Regular Visitor
Regular Visitor

Re: Dynamic Line and Column chart from bipatterns.com

Hi @rdurkin

 

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.

rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

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.

srikolli Frequent Visitor
Frequent Visitor

Re: Dynamic Line and Column chart from bipatterns.com

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

rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

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

anithat Regular Visitor
Regular Visitor

Re: Dynamic Line and Column chart from bipatterns.com

Hi @rdurkin

 

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!

rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

@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

 

 

rdurkin Member
Member

Re: Dynamic Line and Column chart from bipatterns.com

@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

Highlighted
anithat Regular Visitor
Regular Visitor

Re: Dynamic Line and Column chart from bipatterns.com

Hi @rdurkin

 

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!