Forum Discussion
Avoid repeating values for multiple fields placed in matrix visual in Values section.
I do not fully understand what you need, but look at my file, it can help you
Hi Ahmedx ,
Thanks for the technique , it worked but now the problem is the sorting order.
We used Category = Union(distinct(cagegory(category)) , {"Overall","annual sales",Sales % CY})
now i want to sorrt the columns like , cat 1 - cat 4 should be first , then ,overall sales, annual sales , sales % etc.
what i did i created a separate colum in same table called sort column , and try to sort it using this sort column.
When i try to sort this i am getting circular dependency . is there any way we can fix this .
THanks in advance
Sort column = switch(true(),
category ="cat 1",1,
category ="cat 2",1,
.
.
.
category ="cat 5",5,
category = "Overall sales", 6 etc
pls try this
dumpCategory = ADDCOLUMNS(
UNION(DISTINCT('Table'[cat]),{"Overall Category"}), "Order",
SWITCH( TRUE() ,
[cat]= "Category 1",1,
[cat]= "Category 2",2,
[cat]= "Category 3",3,
[cat]= "Category 4",4,
[cat]= "Overall Category",5
))Hi Ahmedx ,
Here when you say [Cat] does it mean category from master table , and if it is from master table how will i call overall category since it will not be present in Master table. becuase we created in dump . so how to call overall category here. the below step since its not present in master table.
[cat]= "Overall Category",5
this is not a master table but with a table I created
ADDCOLUMNS(
Hi Ahmedx ,
This is working now , sort column is created and when i do a sort by column on category using sort , i see some values are missed out in my table.
before sort i see all the values in the table , after sort i dont see Bench count column and Billable % and Bench % are coming as blank and if i revert the sort , i see all values, not sure whats causing the issue. Please help here
Below are my measures:
Em category =ADDCOLUMNS(UNION(DISTINCT(HC_Master[EM Comments Category]),{"Bench %","Bench Count","Billable %","Grand Total"}),"Order",SWITCH(TRUE(),HC_Master[EM Comments Category] ="Bench",1,HC_Master[EM Comments Category] ="Billable",2,HC_Master[EM Comments Category] ="Blocked",3,HC_Master[EM Comments Category] ="Proposed",4,HC_Master[EM Comments Category] ="Long Leave - ML",5,HC_Master[EM Comments Category] ="PPED",6,HC_Master[EM Comments Category] ="Grand Total",7,HC_Master[EM Comments Category] ="Bench Count",8,HC_Master[EM Comments Category] ="Bench %",9,10//HC_Master[EM Comments Category] ="Billable %",10))Bench count =CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),'Em category'[EM Comments Category] IN {"Bench","Blocked","Proposed"})Bench % =VAR total = CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),ALL('Em category'))VAR bench = CALCULATE([Bench count])returnDIVIDE(bench,total)billable % =VAR total = CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),ALL('Em category'))VAR Billable = CALCULATE(DISTINCTCOUNT(HC_Master[GPN]),'Em category'[EM Comments Category]="Billable")returnDIVIDE(Billable,total)
Hi Ahmedx , I tried usint below measure ,
For table , the values are not getting sorted correctly in power bi , and getting blank values.
For Measure : I am getting Circular dependency.
Please advice.
Table :
Em category =ADDCOLUMNS(UNION(DISTINCT(HC_Master[EM Comments Category]),{"Bench %","Bench Count","Billable %","Grand Total"}),"Order",SWITCH(TRUE(),HC_Master[EM Comments Category] ="Bench",1,HC_Master[EM Comments Category] ="Billable",2,HC_Master[EM Comments Category] ="Blocked",3,HC_Master[EM Comments Category] ="Proposed",4,HC_Master[EM Comments Category] ="Long Leave - ML",5,HC_Master[EM Comments Category] ="PPED",6,HC_Master[EM Comments Category] ="Grand Total",7,HC_Master[EM Comments Category] ="Bench Count",8,HC_Master[EM Comments Category] ="Bench %",9,10//HC_Master[EM Comments Category] ="Billable %",10))MeasureSorting = SWITCH(TRUE(),'Em category'[EM Comments Category] ="Bench",1,'Em category'[EM Comments Category] ="Billable",2,'Em category'[EM Comments Category] ="Blocked",3,'Em category'[EM Comments Category] ="Proposed",4,'Em category'[EM Comments Category] ="Long Leave - ML",5,'Em category'[EM Comments Category] ="PPED",6,'Em category'[EM Comments Category] ="Grand Total",7,'Em category'[EM Comments Category] ="Bench Count",8,'Em category'[EM Comments Category] ="Bench %",9,10//'Em category'[EM Comments Category] ="Billable %",10)Share sample pbix file to help you.