SUMX and SUMMERIZE help

Si_7777 , In Such cases, if you add an additional dimension, you have to add the same in Summarize. Usually, Distinctcount ay like level and GT will not match unless you have a common granular group by 

Hi Si_7777,

first thing you should never create a column inside SUMMARIZE but used ADDCOLUMNS to do this, second you do not need SUMMARIZE to group a single cloumn, you can use VALUES, finally you can simplify the CALCULATE call so first change is

SUMX (
    FILTER (
        ADDCOLUMNS (
            VALUES ( DW_SalesIn[TransactionId] ),
            "PCount",
                CALCULATE (
                    DISTINCTCOUNT ( DW_SalesIn[ProductId] ),
                    KEEPFILTERS ( DimCategory[DeptKey] <> 416 )
                )
        ),
        [PCount] = 1
    ),
    [PCount]
)

Try this code and please let me know, we might have to modifiy that a bit more but meanwhile can you show us the model so we can understand where Category comes from?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hi Si_7777 

try below measure and let me know if it works.

SalesInCount_Single_Cat =
VAR TransactionCounts = 
    SUMMARIZE(
        ALL(DW_SalesIn),
        DW_SalesIn[TransactionId],
        "ProductCount",
        CALCULATE(
            DISTINCTCOUNT(DW_SalesIn[ProductId]),
            ALL(DimCategory),
            ALL(DimProduct),
            DimCategory[CategoryKey] <> 416
        )
    )
VAR SingleItemTransactions = 
    FILTER(TransactionCounts, [ProductCount] = 1)
RETURN
    CALCULATE(
        DISTINCTCOUNT(DW_SalesIn[TransactionId]),
        TREATAS(
            SELECTCOLUMNS(SingleItemTransactions, "TID", [TransactionId]),
            DW_SalesIn[TransactionId]
        )
    )

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,
praful

Hi Si_7777 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Praful_Potphode FBergamaschi amitchandak for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.