Summarize columns and add missing items may not be used in this context

[JamesBurke]

Hi All ,

Test 2 = 
 var sourcetable = FILTER(SUMMARIZECOLUMNS('Date'[Date],'Emporia Devices'[Wawrick Device Name (groups)],"RankMeasure",[Rank DeviceName Usage]), 'Emporia Devices'[Wawrick Device Name (groups)] = SELECTEDVALUE('Emporia Devices'[Wawrick Device Name (groups)] ))
RETURN
COUNTX(FILTER(sourcetable,[RankMeasure] = 1),[RankMeasure])

Test 2 Mth = 
 var sourcetable = FILTER(SUMMARIZECOLUMNS('Date'[Mth & Yr],'Emporia Devices'[Wawrick Device Name (groups)],"RankMeasure",[Rank DeviceName Usage]), 'Emporia Devices'[Wawrick Device Name (groups)] = SELECTEDVALUE('Emporia Devices'[Wawrick Device Name (groups)] ))
RETURN
COUNTX(FILTER(sourcetable,[RankMeasure] = 1),[RankMeasure])

Test 2 yr = 
 var sourcetable = FILTER(SUMMARIZECOLUMNS('Date'[Year],'Emporia Devices'[Wawrick Device Name (groups)],"RankMeasure",[Rank DeviceName Usage]), 'Emporia Devices'[Wawrick Device Name (groups)] = SELECTEDVALUE('Emporia Devices'[Wawrick Device Name (groups)] ))
RETURN
COUNTX(FILTER(sourcetable,[RankMeasure] = 1),[RankMeasure])

Rank Total = CALCULATE([Test 2] + [Test 2 Mth] + [Test 2 yr])

Getting this error 

I want to make a total for thr rank and break it down by each year so they can see the change in trophy count by year 

Any help would be appericated 🙂

Thanks , James 

[Anonymous]

Hi, @JamesBurke 

Based on your error message, the use of using Summarize Columns and AddMissingItems in Measure Test 2 may not be used in this context. Usually these functions are used to create tables and not directly in Measure. 

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Learn 

You are using the SUMMARIZECOLUMNS() and FILTER() functions in Measure, which may be the cause of the error.   

You can try using SUMMARIZE() instead of SUMMARIZECOLUMNS(): 

Test 2 =
VAR sourcetable =
    FILTER (
        SUMMARIZE (
            'Date',
            'Date'[Date],
            'Emporia Devices'[Wawrick Device Name (groups)],
            "RankMeasure", [Rank DeviceName Usage]
        ),
        'Emporia Devices'[Wawrick Device Name (groups)]
            = SELECTEDVALUE ( 'Emporia Devices'[Wawrick Device Name (groups)] )
    )
RETURN
    COUNTX ( FILTER ( sourcetable, [RankMeasure] = 1 ), [RankMeasure] )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.