Dax Formula

[hosea_chumba]

Take the example below,

Please assist to come up with a formula that identifies the common items appearing in all the months. 

Date (DD/MM/YYYY)	Item
01/08/2022	X
03/08/2022	Y
01/09/2022	Z
02/09/2022	X
03/09/2022	Y
01/09/2022	W
01/10/2022	X
22/10/2022	Y
01/11/2022	X
11/11/2022	R
23/11/2022	Y
01/12/2022	X
12/12/2022	Y

[tamerj1]

Hi @hosea_chumba 
Apologies for the late reply.

Please refer to attached sample file and below description. Hopping this is what you're looking for.

Place the following filter measure in the filter pane of the table or chart visual and select "is" 1 or "is not blank"

Filter Measure = 
VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Item] ) )
VAR T2 = SELECTCOLUMNS ( T1, "@YearMonth", FORMAT ( [Date], "YYYYMM" ) )
VAR T3 = SELECTCOLUMNS ( ALLSELECTED ('Table'[Date] ), "@YearMonth", FORMAT ( [Date], "YYYYMM" ) )
VAR REsult =
    IF ( 
        ISEMPTY ( EXCEPT ( T3, T2 ) ),
        1
    )
RETURN 
    Result

[FreemanZ]

hi @hosea_chumba 

I can only sovle this with a helper column. 

1) add a calculated column like:

Month = FORMAT([Date], "YYYYMM")

2) create a calculated table like this:

List = 
VAR _monthcount = DISTINCTCOUNT(TableName[Month])
RETURN
FILTER(
    VALUES(TableName[Item]),
    CALCULATE(DISTINCTCOUNT(TableName[Date])=_monthcount)
)

it worked like this:

[FreemanZ]

hi @hosea_chumba 

just realized you have in maximum one row per item every month. 

So the list - calculated table could be achieved without the helper column, like this:

List2 = 
VAR _table = 
ADDCOLUMNS(
    TableName,
    "YYYYMM",
    FORMAT(TableName[Date], "YYYYMM")
)
VAR _monthcount = COUNTROWS(SUMMARIZE(_table, [YYYYMM]))
RETURN
FILTER(
    VALUES(TableName[Item]),
    CALCULATE(DISTINCTCOUNT(TableName[Date])=_monthcount)
)

[hosea_chumba]

@wdx223_Daniel unfortunately this did not work

[tamerj1]

Hi @hosea_chumba 

do you want to count them or display them in a crad visual or just filter a table visual or something else?

[hosea_chumba]

hi @tamerj1 , i want to display them in a table or a tabular visual

[wdx223_Daniel]

NewMeasure =
CALCULATE (
    COUNTROWS (
        SUMMARIZE (
            ADDCOLUMNS (
                VALUES ( 'Sample'[Date] ),
                "YYMM", FORMAT ( 'Sample'[Date], "YYMM" )
            ),
            [YYMM]
        )
    ),
    ALL ( 'Sample'[Date] )
)
    = CALCULATE (
        COUNTROWS (
            SUMMARIZE (
                ADDCOLUMNS (
                    VALUES ( 'Sample'[Date] ),
                    "YYMM", FORMAT ( 'Sample'[Date], "YYMM" )
                ),
                [YYMM]
            )
        ),
        ALL ( 'Sample' )
    )