Dynamic Line and Column chart from bipatterns.com

[Anonymous]

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

[Anonymous · Accepted solution]

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

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

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.

[Anonymous · Accepted solution]

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]

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 · Accepted solution]

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

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

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]

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

[Anonymous · Accepted solution]

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]

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 · Accepted solution]

@anithat

Or were you refferring to something like this:

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

[anithat]

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]

@anithat

Are you looking for something like this?

View Report Here

 

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