Reply
Highlighted
Member
Posts: 69
Registered: ‎06-22-2018
Accepted Solution

Hot to show (calc) column values as categories on line/bar chart

I have a basic table that looks like this;

 

CIDSDSD2SD3SD_flagSD2_flagSD3_flag
674432177354531  1
674433177354531  1
6744341773545311  
6744351773545311  
6744361773545311  
674437177354531 1 
674438177354531 1 
6744391773545311  
674440177354531 1 
6744411773545311  
674442177354531  1

 

I need to be able to 'group' or 'bucket/bin' the SD, SD2,  SD3 values on the axis and sum up the flags for each one. So the resulting chart should look like this;

SDbuckets.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any ideas how this can be achieved using DAX (or any other way) from the table above?


Accepted Solutions
Member
Posts: 47
Registered: ‎11-06-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Ok, here's a better solution.  No need for the calculated table from before.  Create a new calculated column (Modeling > New Column) with the following DAX:

 

Bucket = IF(Table1[SD_flag] = 1, "SD1", IF(Table1[SD2_flag] = 1, "SD2", "SD3"))

 

Then the above "Bucket" calculated column will be the Axis, and "Count of Bucket" will be the value.

 

With this solution, you'll be able to add a slicer for CID and the chart will update properly.

View solution in original post


All Replies
Super User
Posts: 10,499
Registered: ‎07-11-2015

Re: Hot to show (calc) column values as categories on line/bar chart

Is this the raw data or what you have in DAX? I ask because if it is raw data you may want to pivot it.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Member
Posts: 47
Registered: ‎11-06-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Assuming your table is your raw data, you could do something like this with a calculated table (measure > new table), but it's probabaly not the cleanest way to do it, and would require further work if you add in more SD# buckets.

 

Table = UNION(ROW("Bucket", "SD1" , "Value", COUNTROWS(FILTER(Table1, Table1[SD_flag] = 1))), ROW("Bucket", "SD2" , "Value", COUNTROWS(FILTER(Table1, Table1[SD2_flag] = 1))), ROW("Bucket", "SD3" , "Value", COUNTROWS(FILTER(Table1, Table1[SD3_flag] = 1))))

Then you'd add "Bucket" as the Axis, and Value as the value on a line or bar chart.

Community Support Team
Posts: 2,926
Registered: ‎02-06-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Hi @rax99,

 

By my tests, the solution of ssugar should be useful. 

 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 69
Registered: ‎06-22-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Thanks this does work, however when I filter using values from another table this doesnt filter through on this chart. How can I build this same logic but also include the CID as this will be the logical join onto other datasets.

 

And yes this is the RAW table.

Member
Posts: 47
Registered: ‎11-06-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Ok, here's a better solution.  No need for the calculated table from before.  Create a new calculated column (Modeling > New Column) with the following DAX:

 

Bucket = IF(Table1[SD_flag] = 1, "SD1", IF(Table1[SD2_flag] = 1, "SD2", "SD3"))

 

Then the above "Bucket" calculated column will be the Axis, and "Count of Bucket" will be the value.

 

With this solution, you'll be able to add a slicer for CID and the chart will update properly.

Community Support Team
Posts: 2,926
Registered: ‎02-06-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Hi @rax99,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 69
Registered: ‎06-22-2018

Re: Hot to show (calc) column values as categories on line/bar chart

Simple and effective. Thanks very much