Count Occurrence and use it as filter in calculate

[Anonymous]

Date                           Id_Count(flag=1)                 id_Count(Occurred all 5 days)           id_Count(Occurred 4 days)
15/09/2021                          20                                                13                                                           5
16/09/2021                          24                                                13                                                           6
17/09/2021                          19                                                 13                                                          2
18/09/2021                          27                                                 13                                                          6
19/09/2021                          15                                                  13                                                         3
Total                                     27                                                    13                                                      6

Hi guys,

Thank you for help.

I have 2 columns where i have Date and IDs. So in the table above i count the Distinct IDs in the second column by doing a distinct count.

I need to count the IDs which occured all 5 days. NExt the third column that shows 13 in all rows is the column i need which means out of 20 there are 13 IDs that occured on all 5 days. similarly for 4 times occurence in 4th column . And what i really need to display in the second table is the table where it says

Total IDs                   IDs 5 days                           IDs 4 Days                 and so on.

27                                  13                                           6

Any help is much appreciated.

Please let me know if more info is required.

What i have tried is i was able to count the distinct count of date based on IDs which gives me 5,4,3,2,1 per ID. but when i use something like this Calculate(Count(ID), filter(Count(Date) = 5), The filter does not get applied. It is understandable as the only thing i am displaying in the first table visual is On the date basis so it has the aggrgated value 5 for each date in the visual and hence does not filter out 4,3,2,1 in counting. 

Thanks again.

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler  I will try to explain.

-- I have 3 cols in table

--Col1 Date : I have 5 date entries as mentioned in above post

-- Col2 ID : Every date has total of 50 IDs (100,101,102,103....150)

-- Col3 flag = has values 1 and 0

I need to output tables.

First table as mentioned in above post has 4 cols 

Col1 : Date from input table,

Col2 : Count of ID where flag = 1

Col3 : Count of ID where flag =1 and occured on all 5 days (occured means has value for a date)

Col4 : Count of ID where flag = 1 and occured on 4 days (out of 5)

Date                           Id_Count(flag=1)                 id_Count(Occurred all 5 days)           id_Count(Occurred 4 days)
15/09/2021                          20                                                13                                                           5
16/09/2021                          24                                                13                                                           6
17/09/2021                          19                                                 13                                                          2
18/09/2021                          27                                                 13                                                          6
19/09/2021                          15                                                  13                                                         3
Total                                     27                                                    13                                                      6

My Table 2 output is just the aggegation 

Total distinct IDS with flag 1                                                 27           

Total distinct IDs with flag 1 and occured all 5 days            13

Total distinct IDs with flag 1 and occured 4 days                  6

Hope it makes sense.

Appreciate help. Thanks

[Greg_Deckler]

@Anonymous Try:

Table34a = 
    VAR __Table0 = FILTER('Table34',[Column]=1)
    VAR __Table1 = SUMMARIZE(__Table0,[Value],"Count",COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table0,"Date",[Date]))))
    VAR __Table2 = 
        ADDCOLUMNS(
            DISTINCT(SELECTCOLUMNS('Table34',"__Date",[Date])),
            "Id_Count",COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table0,[Date]=[__Date]),"ID",[Value]))),
            "Id_Count5",COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[Count]=5),"ID",[Value]))),
            "Id_Count4",COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[Count]=4),"ID",[Value])))
        )
RETURN
    __Table2

[Anonymous]

@Greg_Deckler , Thnaks for reply. I cannot understand what you have posted. (not all of it) Could you please help me understand the code? Only then i would be able to test it on my dataset.

Thank you.

[Greg_Deckler]

@Anonymous Attaching the PBIX below sig for reference. Comments inline:

Table34a = 
    VAR __Table0 = FILTER('Table34',[Column]=1) //[Column] is your "Flag" column so basically get all rows in the table where the flag column is 1

    VAR __Table1 = SUMMARIZE(__Table0,[Value],"Count",COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table0,"Date",[Date])))) //Summarize the filtered table by [Value] colum and count the number of rows. Here [Value] represents your "ID" column 
// The rest of the code grabs the unique dates out of your original source table and adds columns that count the things you want to count. The first counts all the distinct IDs. The next two count the distinct ID's that appear for 5 dates and 4 dates.
    VAR __Table2 = 
        ADDCOLUMNS(
            DISTINCT(SELECTCOLUMNS('Table34',"__Date",[Date])),
            "Id_Count",COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table0,[Date]=[__Date]),"ID",[Value]))),
            "Id_Count5",COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[Count]=5),"ID",[Value]))),
            "Id_Count4",COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[Count]=4),"ID",[Value])))
        )
RETURN
    __Table2

[Anonymous]

@Greg_Deckler Thanks a lot. Let me try that : )