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/2022X
03/08/2022Y
01/09/2022Z
02/09/2022X
03/09/2022Y
01/09/2022W
01/10/2022X
22/10/2022Y
01/11/2022X
11/11/2022R
23/11/2022Y
01/12/2022X
12/12/2022Y

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago
    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' )
    )
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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?

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

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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