Help with Custome Column

[GomzeeR]

Hi all,

I am still new to Power BI and I have a problem that I am unable to solve.

I have records with Order numbers with different charge Type Code.

I could manage to capture all the order numbers with Charge Type Code "1".

But I also want to capture the Order numbers that does not have Charge Type Code "1".

What ever Logic I use, I am unable to achieve the Count for the Order Numbers without the Charge Type Code "1".

I have used the below formula to get the Order numbers for Charge Type Code "1".

If(Table3[Charge Type Code] = "1", 1,0)

Appreciate any help with this.

Kind regards,

Gowtham

[amitchandak · Accepted solution]

Try like

Charge type order 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,1,0))
		
Charge type order not 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,0,1))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[dax · Accepted solution]

Hi @GomzeeR , 

I am not clear about your requirement, if you want to get distinct count of order number, I think you could try below measures

Measure = CALCULATE(DISTINCTCOUNT('Table'[Order No.]), 'Table'[Charge Type Code]="1")
Measure2 = CALCULATE(DISTINCTCOUNT('Table'[Order No.]), 'Table'[Charge Type Code]<>"1")

If not, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

Try like

Charge type order 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,1,0))
		
Charge type order not 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,0,1))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[vivran22]

Hi Gowtham,

Please try following measures:

Count Charge type 1  = COUNTAX(table3, IF(Table3[Charge Type Code] = "1", [Charge Type Code]))

Count Charge type not 1  = COUNTAX(table3, IF(Table3[Charge Type Code] <> "1", [Charge Type Code]))

Regards,

Vivek

[GomzeeR]

Hi Vivek,

Thank you for the reply.

This formula is not working. It counts the Order number multiple times as there are multiple Charge Codes for one order number.

Ex: One Customer has 219 Unique order numbers, and there are 50 orders Charged, so I need to get 169 as the value for the orders that didnt have a "1" Charge.

It is very tricky.

Kind regards,

gowtham

[vivran22]

May I request you to share the sample data file with the required output as an example.