Occurrence Issue

[Jiangmei_wu]

Hello,

The occurrence dax below can get the correct results with small test sample generated from real date, but when it's with real data, the resuts are incorrect. do you know why?

1 time =

VAR SummaryTable =

    SUMMARIZE( 'Sheet1', Sheet1'[Name], 'Sheet1'[Week number],  "Occurrences",

        CALCULATE(COUNTROWS('Sheet1'),

            ALLEXCEPT('Sheet1', 'Sheet1'[Name], 'Sheet1'[Week number])))

RETURN

COUNTROWS(

    FILTER( SummaryTable,  [Occurrences] = 1))

Thanks, Mei

[MeiJiang]

Can someone resolve it?

for date Sept 3rd, Location A, here are the results should be: 

Location	A
Occurrence	Count
Five	1
One	47
Three	5
Two	10

Here is Power BI Results:

Please see attached:

Occurrence.pbix

Sample - Validate Occurrence.xlsx

Many thanks,

Mei

[Jiangmei_wu]

@v-tangjie-msft 

Hi Neeco, hope all is well! you have helped me resolved one problem. Hope you could squeeze some times to look into my another issue - Occurrence issue (please don't use occurrence column in Excel as it is mannually added to use for validation) which no one has been able to resolved it.

Thanks in advance!

Mei

[Jiangmei_wu · Accepted solution]

Hi Neeco, please discard the request above. I found a solution as below. Thanks, Mei

Names_occurred_3 =

DIVIDE(

    CALCULATE(

    COUNTROWS('Raw Data'),

    FILTER(

        'Raw Data',

        not(ISBLANK('Raw Data'[Name])) &&

        COUNTROWS(

            FILTER(

                'Raw Data',

                'Raw Data'[Name] = EARLIER('Raw Data'[Name])

            )

        ) = 3

    )

), 3

)

[parry2k]

@Jiangmei_wu it will be easier if you share some sample data with the expected output to help. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Jiangmei_wu]

Hi Parry,

Here is the excel sample data:

https://1drv.ms/x/s!Apm9aBtUDQ5yihI0wWHxSctdjOun?e=KOerr9

Here are the correct results:

Thanks,

Mei