topic Re: Count IF in DAX Commands and Tips

Count IF

Anonymous — Mon, 30 May 2022 20:38:07 GMT

Hello friends, how are you?

I need to count the balls different of green, someone help me to create this measure?

this measure is the average of a values in a period of time.

thanks a lot!

Re: Count IF

Whitewater100 — Mon, 30 May 2022 23:38:09 GMT

Hi:

What are the expected results?

Can you share some example data?

* If you wants a Daily or Monthly average it is important to have a date table, marked as a date table (on date field). I'll paste some DAX Date table code below, in case you need it.

Go to Modeling>NEW Table: (see below).

Now measures after you get your date table set and connected to your fact table on date fields.

example of measure we want average on:

Total Sales = SUM(Table[Sales Amt])

[Daily Sales Average] =averagex( Dates', [Total Sales])

[Daily Sales Average] =

averagex(

'Dates',

calculate(

[Total Sales],

Transaction_data[Sales_amount] <> 0

)

[Monrthly Sales Average] =

averagex(

'Dates'[Month],

calculate(

[Total Sales],

Transaction_data[Sales_amount] <> 0

)

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Re: Count IF

Anonymous — Wed, 01 Jun 2022 13:22:30 GMT

Whitewater100

Hello, I have a primary table:

Ball Logics: if(% < 90% Yellow ball, % > 110% Red ball, % between 90% and 110% green ball)

Date	Item	%	Ball collour
26/04/2022	Item A	113,9%	Yellow ball
13/05/2022	Item A	97,7%	Green ball
16/05/2022	Item A	97,7%	Green ball
18/05/2022	Item A	85,0%	Red ball
25/04/2022	Item A	100,3%	Green ball
25/05/2022	Item A	100,3%	Green ball
27/04/2022	Item A	100,3%	Green ball
18/05/2022	Item A	81,0%	Red ball
18/05/2022	Item A	89,0%	Red ball
26/04/2022	Item B	113,9%	Yellow ball
13/05/2022	Item B	97,7%	Green ball
16/05/2022	Item B	97,7%	Green ball
18/05/2022	Item B	85,0%	Red ball
25/04/2022	Item B	100,3%	Green ball
26/04/2022	Item B	100,3%	Green ball
27/04/2022	Item B	91,0%	Green ball
18/05/2022	Item B	70,0%	Red ball
18/05/2022	Item B	50,0%	Red ball

From this table, I make a avarage table:

Average	%	Ball collour
Item A	96,1%	Green ball
Item B	89,5%	Red ball

I need to count in this Avarage table how many itens appears different of green ball, can you help me?

thanks a lot.

Re: Count IF

v-zhangti — Thu, 02 Jun 2022 02:20:47 GMT

Hi, Anonymous

You can try the following methods.

Table:

Average = SUMMARIZE ( 'Table', 'Table'[Item], "Average", CALCULATE ( AVERAGE ( 'Table'[%] ), FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) ) ) )

Column:

Ball collour = IF ( [Average] < 0.9, "Yellow ball", IF ( [Average] > 1.1, "Red ball", "Green ball" ) )

Measure:

Count = CALCULATE ( COUNT ( 'Average'[Item] ), FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" ) )

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

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

Re: Count IF

Whitewater100 — Thu, 02 Jun 2022 11:11:01 GMT

Hello Again:

Since there is one yellow ball I made % both including Yellow and not including yellow. Please see file link.https://drive.google.com/file/d/1fw1_03W0Pn_eOG8CdKhOvSiwVrcXrGru/view?usp=sharing

Re: Count IF

Anonymous — Thu, 02 Jun 2022 19:18:08 GMT

Whitewater100

v-zhangti

Thanks a lot !!

Have a nice week.

Re: Count IF

Whitewater100 — Thu, 02 Jun 2022 19:20:31 GMT

Hello:

Glad it worked. Are you able to mark my reply with solution or kudos? I appreciate that. Thanks again..

Re: Count IF

Anonymous — Thu, 02 Jun 2022 19:33:00 GMT

Whitewater100

Sure, done, thanks again !!!