Group values from field with condition

[Anonymous]

Dear users,

I have a table with :

Vehicule - Line 

d4564564   -   M   

d7164564   -   L    

d4544575   -   P    

d4747422   -   M  

d4887564   -   O  

d4564564   -   M  

d4564564   -   L   

I want to display those values in a bar chart by group some lines values, like this :

Line - Count Vehicule

L = 2

M = 3

O = 1

P = 1

M-O = 4 

L-P = 3

=> How can I group like this ?

Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous ,

First create 2 tables as below:

Table 1 = 
ADDCOLUMNS('Table',"line2",'Table'[Line ])

Table 2 = CROSSJOIN(DISTINCT('Table'[Line ]),DISTINCT('Table 1'[line2]))

Then you will get all the combinations of the line;

Create a calculated column as below:

Line = IF('Table 2'[Line ]='Table 2'[line2],'Table 2'[Line ],CONCATENATE('Table 2'[Line ]&"-",'Table 2'[line2]))

And a measure as below:

Count Vehicule = IF(MAX('Table 2'[Line]) in FILTERS('Table'[Line ]),COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[Line])),'Table'[Line ]),COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[Line ])),'Table'[Line ])+COUNTX(FILTER('Table','Table'[Line ]=MAX('Table 2'[line2])),'Table'[Line ]))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[amitchandak]

@Anonymous , see if segmentation can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[ryan_mayu]

@Anonymous 

Maybe you can transform the table.

Table 3 = 
VAR TBL1= SUMMARIZE('Table (3)','Table (3)'[line],"count vehicule",count('Table (3)'[vehicule]))
VAR TBL2= SUMMARIZE('Table (3)',"line","M-O","count vehicule",COUNTX(FILTER('Table (3)','Table (3)'[line]="M" ||'Table (3)'[line]="O"), 'Table (3)'[vehicule]))
VAR TBL3=SUMMARIZE('Table (3)',"line","L-P","count vehicule",COUNTX(FILTER('Table (3)','Table (3)'[line]="L" ||'Table (3)'[line]="P"), 'Table (3)'[vehicule]))
RETURN UNION(TBL1,TBL2,TBL3)

[Pragati11]

Hi @Anonymous ,

You can achieve this as follows:

1. Create a Clustered Bar chart in Power BI
2. Drag "LINE" column to Axis
3. Move COUNT of "VEHICLE" column in Values option.

Do you need grouping like "M-O" and "L-P" along with the existing values in "LINE" column?

Thanks,

Pragati