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

[Anonymous]

I have a basic table that looks like this;

CID	SD	SD2	SD3	SD_flag	SD2_flag	SD3_flag
674432	177	354	531			1
674433	177	354	531			1
674434	177	354	531	1
674435	177	354	531	1
674436	177	354	531	1
674437	177	354	531		1
674438	177	354	531		1
674439	177	354	531	1
674440	177	354	531		1
674441	177	354	531	1
674442	177	354	531			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;

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

[v-piga-msft]

Hi @Anonymous,

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

[Greg_Deckler]

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.

[ssugar]

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.

[Anonymous]

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.

[v-piga-msft]

Hi @Anonymous,

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

[ssugar · Accepted solution]

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.

[Anonymous]

Simple and effective. Thanks very much