topic Re: Count the number of occurrence of a value in DAX Commands and Tips

Count the number of occurrence of a value

tbhadmus — Thu, 23 Jun 2022 12:03:27 GMT

I want to get the number of occurrences of each DQV Category. The QDV Category is a result of a measure based on the Overall Average score. For instance, "Meeting Standard" appears only once based on the selected Filter but I am getting 0.
Any help would be highly appreciated.

Re: Count the number of occurrence of a value

tamerj1 — Thu, 23 Jun 2022 12:40:45 GMT

Hi tbhadmus
Following is the general idea. In order to include the category slicer with the formula I need more information about this slicer and the measure itself.

Count = VAR T1 = SUMMARIZE ( TableName, TableName[Country], TableName[IP] ) VAR T2 = ADDCOLUMNS ( T1, "@QDV_Category", [QDV Category] ) VAR T3 = FILTER ( T2, [@QDV_Category] = "Meeting Standard" ) RETURN COUNTROWS ( T3 )

Re: Count the number of occurrence of a value

tbhadmus — Thu, 23 Jun 2022 12:54:38 GMT

Hi tamerj1,
thanks for your response.
The slicer represents the Fiscal Year. If I select a fiscal year, the total occurrence of the DQV category should be displayed.

Re: Count the number of occurrence of a value

tamerj1 — Thu, 23 Jun 2022 12:57:19 GMT

tbhadmus
Ok then the same code should work. Please try and let me know. Thank you

Re: Count the number of occurrence of a value

tbhadmus — Thu, 23 Jun 2022 13:00:46 GMT

I tried the formula but it's the same as what I initially had:

VAR numb =

CALCULATE(

DISTINCTCOUNT('Implementing Partner'[Implementing Partner]),

FILTER(

ALLSELECTED('Implementing Partner'[Implementing Partner]),

'1_DQA Measures'[QDV Category] = "Meeting Standard"

)

RETURN

IF(NUMB <> 0,numb, 0)

Re: Count the number of occurrence of a value

tbhadmus — Thu, 23 Jun 2022 13:04:17 GMT

The code works if no filter is selected, but when I select a filter, it gives me a value of zero instead of 1 from the picture I posted.

Re: Count the number of occurrence of a value

tamerj1 — Thu, 23 Jun 2022 13:43:03 GMT

tbhadmus
You mean when you elect a fiscal year? It should work unless I'm missing something. Would you please provide more details about the data model and tables? Perhaps some screenshots?

Re: Count the number of occurrence of a value

v-zhangti — Wed, 29 Jun 2022 08:49:54 GMT

Hi, tbhadmus

Can you provide some sample data? And what you expect the output to be, which can be shown in pictures.

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 the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 07:43:09 GMT

tamerj1 thanks for your response. The problem was with my filters and I already figured it out. Thanks a lot for the help!

I have another challenge which I need help with.

In the table above, I want to calculate the NextDVDate based on the values in the Average M&E column. The Average M&E column is a measure and the condition for the calculation is as below:

But I am getting an error in my calculation. Any help will be higlhy appreciated.
Thanks in advance.

Re: Count the number of occurrence of a value

tamerj1 — Sat, 02 Jul 2022 08:58:22 GMT

Hi tbhadmus

please try

NextDVDate = VAR avgScore = [Average M&E] VAR date_ = SELECTEDVALUE ( 'Data Element Values'[PeriodDate] ) RETURN SWITCH ( TRUE (), avgScore >= 95 && avgScore <= 100, ( date_ + 365 ), avgScore >= 80 && avgScore <= 94, ( date_ + 365 ), avgScore < 80, ( date_ + 90 ), BLANK () )

Re: Count the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 13:01:57 GMT

Hi @tamerj1, thanks for your swift response.
I tried the above formula but I got Blank as my result. Below is the table using the formula above:

Re: Count the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 13:34:43 GMT

The problem here is that the "date_" variable is returning Blank().

Re: Count the number of occurrence of a value

tamerj1 — Sat, 02 Jul 2022 13:49:32 GMT

tbhadmus

Looks like PeriodDate is a measur? In this case no need for SELECTEDVALUE just refer to the measure directly

Re: Count the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 13:54:56 GMT

Column PeriodDate is a Calculated Column. Find below:

PeriodDate = IF(NOT CONTAINSSTRING([Period], "Q"), (DATE(([Period]-MOD([Period],100))/100, MOD([Period],100), 1)), BLANK())

Re: Count the number of occurrence of a value

tamerj1 — Sat, 02 Jul 2022 14:20:50 GMT

Please try by wrapping SELECTEDVALUE with CALCULATE

Re: Count the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 15:34:43 GMT

I did that as well but I still got Blank()

Re: Count the number of occurrence of a value

tamerj1 — Sat, 02 Jul 2022 15:51:36 GMT

Are you creating a calculated column or a measure?

Re: Count the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 15:56:59 GMT

~~I'm creating a measure. The "PeriodDate" is a calculated column.~~

Re: Count the number of occurrence of a value

tamerj1 — Sat, 02 Jul 2022 16:04:25 GMT

What are slicing by in your matrix?

Re: Count the number of occurrence of a value

tbhadmus — Sat, 02 Jul 2022 16:06:44 GMT

I'm slicing by the countries