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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rax99
Helper V
Helper V

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?

1 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.

View solution in original post

7 REPLIES 7
v-piga-msft
Resident Rockstar
Resident Rockstar

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.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.

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.

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.

Simple and effective. Thanks very much

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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