Count Uniq ID, IF value exist anywhere in column2

[NilR]

I need a measure to Distinctcount([IDs]),

I need two set of filters first to check IF anywhere in [count] column the data is equal 1, contains( [Count],1)  in my date range,Then select those uniq IDs and filter them agian where [Active months] >=11 and [Flag] = y, it seems I need nested filter or something similar.

This is what I wrote:

var _Max_Date = MAX('Calendar Service'[Date])
VAR _ENR_DATE = EOMONTH(_Max_Date,-2)+1
var _Max_12_MOS = date(year(_Max_Date),Month(_Max_Date)-11,1)

VAR _gpnb = MAX(LIST[GP])
VAR _ID =
GROUPBY (
    FILTER (
        'Table',
        [Start_Date] >= _ENR_DATE
            && [Start_Date] <= _Max_Date
            && [GP] = _gpnb
    ),
    [ID]
)
VAR _count =
CALCULATETABLE (
    SUMMARIZE (
        'Table',
        [GP],
        [ID],
        [FLAG],
        [Active_months],
        "HAD_WELL", SUM ( 'Table'[Count] )
    ),
    FILTER (
        'Table',
        [GP] = _gpnb
            && [ID]
                IN _ID
                    && ( [Start_Date] >= _Max_12_MOS
                    && [Start_Date] <= _Max_Date )
                    && 'Table'[count]
                        = MAX ( [count] )
    )
)

return 
CALCULATE(SUMX(_count,IF([count]>=1,1,0)), FILTER(_count, [FLAG] = "Y" && [Active_months] >= 11 ))

GP	ID	COUNT	Start_Date	Active__months	FLAG
94	212	0	03/01/2022	12	Y
94	212	0	02/01/2022	12	Y
94	212	0	01/01/2022	12	Y
94	212	0	12/01/2021	11	Y
94	212	1	11/01/2021	10	N
94	212	0	10/01/2021	9	N
94	3000	1	03/01/2022	12	Y
94	3000	1	02/01/2022	12	Y
94	3000	0	01/01/2022	12	Y
94	3000	0	12/01/2021	11	Y

Result:

[selimovd · Accepted solution]

Hey @NilR ,

that should be possible in one calculation.

What do you want the output to be? A calculated column that shows 1 or 0?

I don't have your data model, but based on your result table, the following calculated column should return the result you want:

FilterCurrentRow =
IF (
    -- Checks if for the current ID there is a Count = 1
    CALCULATE (
        COUNTROWS ( myTable ),
        myTable[COUNT] = 1,
        ALLEXCEPT ( myTable, myTable[ID] )
    ) > 0
    -- Checks if active_months >= 11
    && myTable[Active__months] >= 11
    -- checks if Flag = "Y"
    && myTable[FLAG] = "Y",
    1,
    0
)

The result would then look like this:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[tamerj1]

Hi @NilR 
Please try https://www.dropbox.com/t/UwGLd2yKQjN3TunS

Count Measure = 
VAR T1 =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        'Table'[FLAG]
    )
VAR T2 = 
    FILTER ( T1, 'Table'[FLAG] <> "N" )
RETURN
    COUNTROWS ( T2 )

[NilR]

Thanks @tamerj1 !
I also need to test [count] column first to make sure it is included value 1 before I do the count and test for flag. My problem is that the value does not align to flag column all the time so I have to check if this value ever existed anywhere in this column in this selected date range, now check those IDs for Flag = Yes and then finally count them.

[tamerj1]

@NilR 
So basically you need to recreate the flag column but with correct results. I believe this is the simplest approach. What do you think?

[tamerj1]

@NilR 
Here is my proposed solution. https://www.dropbox.com/t/ORfzxAxCo32Cb3Sk

FLAG NEW = 
VAR CurrentMonth = 'Table'[Active__months]
VAR CurrentIDTable = 
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table','Table'[ID] ) )
VAR FilteredTable =
    FILTER ( CurrentIDTable, 'Table'[COUNT] = 1 )
RETURN
    IF ( 
        CurrentMonth >= 11 && COUNTROWS ( FilteredTable ) > 0,
        "Y",
        "N"
    )

Count Measure = 
VAR T1 =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        'Table'[FLAG NEW]
    )
VAR T2 = 
    FILTER ( T1, 'Table'[FLAG NEW] <> "N" )
RETURN
    COUNTROWS ( T2 )

[NilR]

Thanks @tamerj1 ! what I am stuck is if the selected range is different and count is not equal to 1 then the new flag should be N. lets say the count on Jan 11, 2021 is 0 and Jan 10 2021 on your data set is 1. if my selection is from Jan 11,2021 then I do not have anywhere on my date range count = 1 and my new flag should be N.

[tamerj1 · Accepted solution]

@NilR 
In this case, it needs to be a measure from scratch. https://we.tl/t-FqbJPF6cvc

Count Measure = 
VAR T1 = 
    ADDCOLUMNS (
        'Table',
        "@Flag",
            VAR CurrentMonth = 'Table'[Active__months]
            VAR CurrentIDTable = 
                CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table','Table'[ID] ) )
            VAR FilteredTable =
                FILTER ( CurrentIDTable, 'Table'[COUNT] = 1 )
            RETURN
                IF ( 
                    CurrentMonth >= 11 && COUNTROWS ( FilteredTable ) > 0,
                    "Y",
                    "N"
                )
    )
VAR T2 =
    SUMMARIZE (
        T1,
        'Table'[ID],
        [@Flag]
    )
VAR T3 = 
    FILTER ( T2, [@Flag] <> "N" )
RETURN
    COUNTROWS ( T3 )

[NilR]

Thank you @tamerj1 !

[selimovd · Accepted solution]

Hey @NilR ,

that should be possible in one calculation.

What do you want the output to be? A calculated column that shows 1 or 0?

I don't have your data model, but based on your result table, the following calculated column should return the result you want:

FilterCurrentRow =
IF (
    -- Checks if for the current ID there is a Count = 1
    CALCULATE (
        COUNTROWS ( myTable ),
        myTable[COUNT] = 1,
        ALLEXCEPT ( myTable, myTable[ID] )
    ) > 0
    -- Checks if active_months >= 11
    && myTable[Active__months] >= 11
    -- checks if Flag = "Y"
    && myTable[FLAG] = "Y",
    1,
    0
)

The result would then look like this:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[NilR]

@selimovd Thank You!

I am very new to PBI and I was wondering how can I convert this to measure to also add some more dynamic filters?

[ID] IN _ID && ([Start_Date] >= _Max_12_MOS
            && [Start_Date] <= _Max_Date)