Count the number of occurrence of a value

[tbhadmus]

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.

[tbhadmus]

@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.

[tamerj1]

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 ()
    )

[tbhadmus]

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

[tbhadmus]

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:

[tamerj1]

@tbhadmus 

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

[tbhadmus]

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())

[tamerj1]

Please try by wrapping SELECTEDVALUE with CALCULATE 

[tbhadmus]

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

[tamerj1]

Are you creating a calculated column or a measure?

[tbhadmus]

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

[tamerj1]

What are slicing by in your matrix? 

[tbhadmus]

I'm slicing by the countries

[tamerj1]

Can you please change SELECTEDVALUE to MAX or MIN. See first which date yiu get. RETURN date_

[tbhadmus]

Great! MIN seems to work, it returns the same date as the PeriodDate which is what I am trying to do.

[v-zhangti]

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.

[tbhadmus]

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)

[tamerj1 · Accepted solution]

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 )

[tbhadmus]

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.  

[tamerj1]

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

[tbhadmus]

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.