Count Menu_No by number of appearances on a check (Not by the counts)

[Bgonen777]

in the example below I am trying to total each Menu_No for each CheckID 

I tried the following DAX but I keep getting an error: (expressionrefers to multiple columns and can not be converted to scalar value)

CountAppearances = SUMMARIZE(

    POS_CMI,POS_CMI[site_seq],

    POS_CMI[BusinessDate],

    POS_CMI[MENU_NO],

    "Appearances",DISTINCTCOUNT(POS_CMI[Identifier]))

[v-yohua-msft]

Hi, @Bgonen777 

When a DAX expression tries to perform an operation that requires a single value, but the expression returns a table of values, you typically see an error message.
Maybe you can try a DAX expression:

CountAppearances = 
SUMMARIZE(
    POS_CMI,
    POS_CMI[site_seq],
    POS_CMI[BusinessDate],
    POS_CMI[MENU_NO],
    "Appearances", CALCULATE(DISTINCTCOUNT(POS_CMI[Identifier]))
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bgonen777]

I keep getting the same error: "the expression refers to multiple columns,,multiple columns cannot be converted to a scalar value"

I wish there was a way to add a tiny excel file in here.

This is what I am trying to acheive:

Instead of sum up all the counts (A) 

I am trying to reflect the menu item on the check. 

so if the item reflected 6 times it does not count as 6 but as 1 (if the check itself appear twice it still will count as one time)

I tried to add an identifier  (contain site ID- Tran Date- check ID) and then tried DISTINCTCOUNT of the identifier and it doesn't work.

[ryan_mayu]

could you pls provide the  sample data (not the screenshot)?