Forum Discussion

hosea_chumba's avatar
hosea_chumba
Icon for Helper I rankHelper I
3 years ago

Dax Formula

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/...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

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