SUMX and SUMMERIZE help

[Si_7777]

I am trying to calcuate orders that only have 1 item on them.

I have written 

SalesInCount_Single =

    SUMX(

    FILTER(

      SUMMARIZE(

            DW_SalesIn,

            DW_SalesIn[TransactionId],"PCount",

        CALCULATE(DISTINCTCOUNT(DW_SalesIn[ProductId]),FILTER(DimCategory,DimCategory[DeptKey] <> 416))),

        [PCount] = 1), [PCount])

And this works in the context of order type.  Order 91 only has 1 item.

 

When I try to add Category it breaks down, I have tried variations of grouping and ignoring the category row context.

At category level I only want to see order 91 show 1

Any one help?

[Si_7777 · Accepted solution]

Thank you every one.  In the end I created a table using summerize and joined that in the model to the sales fact table with filter direction of both.  This returnes the expected result. 

[FBergamaschi]

Be careful on bidirectional filtering, it can create ambiguity if you have more than one fact table.

I suggest to replace the direction to single and inject a call to CROSSFILTER in a calculate statement, so you can setup that relationship as bidirectional ONLY for the time of the calculation and not always.

If you want help on that, send me a direct message and then we post the solution here

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

[v-venuppu]

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.

[Praful_Potphode]

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

[Si_7777]

Wow thank you it did return values which seemed correct but some categories I could not verify

[FBergamaschi]

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

[Si_7777]

Thank you for your response, very interesting.

The Add column method works the same as using summerize and produces the same result at order type and Im sure performance may be better.

Order 91 is only 1 product

But this still doesnt work for across categories.  My model is Sales>Product>Category.

[FBergamaschi]

Thanks but to help you in perfect way I would need to have the model visibile or have a sample data

What I can try is suggest this variation but again I am blind without the model

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

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

[Si_7777]

Thanks for your help, unfortunately I can't share any data.  The tables in question are modelled as below. 

[FBergamaschi]

So the DAX still does not work?

In case not, I would like to complete the thing as it down not look like anything complex

Can we complete these task connecting online and you show me the issue?

My mail

francesco.bergamaschi@kubisco.com

If you want to settle a timing

best

FB

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

[Si_7777]

Thank you very kind offer.

[amitchandak]

@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