Create a custom table to count ItemName and Occurence

[Anonymous]

Hi,

Hoping this will be a simple answer to someone, will be researching also and copy the answer and refference if i find it out there.

A few days back i made this post;

http://community.powerbi.com/t5/Desktop/Finding-Data-insights-by-product-refference/td-p/149710

And the solution to that issue is now resolved, but i now want to make another table that lists each Item Description and Counts the ammount of times its has been sold.

Reason been, i will then have a distinc list of Product Names and a count of times sold to create a relationship with the other new table containing items sold with other items and the count that pattern has happened.

So i can create a Measure that finds the % those two items have been sold together overall.

Whereas at the moment i have the Product A and B have been sold together 150 times, but i dont know the ammount of times Product A was sold overall to calculate there compatability %.

Thanks in Advance,

J

[OwenAuger · Accepted solution]

Hi @Anonymous

I would recommend you follow the Basket Analyis pattern on DAX Patterns.

This would apply to your earlier post as well.

The pattern, as presented on that page, allows you to create a measure calculating the frequency of co-occurence of products in orders. There is also an example of an [Orders with Both Products %] measure on that page.

The advantage of doing this with measures (rather than pre-calculating tables) is that the results respond to filter context.

Cheers,

Owen

[Anonymous]

@OwenAuger Its took some doing but i got there is the end!!

That Dax pattern essencially works just took me a little time to get my head around it,

Thanks,

Josh

[OwenAuger]

Hi @Anonymous,

You're welcome - glad it's making sense!

Had an unusually busy week so hadn't managed to reply yet.

Cheers,

Owen

[Anonymous]

@OwenAuger

Hi Owen,

Looking at the examples in the scenario its perfect (in a pivot table) struggling in Power BI though unless im missing something.

I have my main Product Analysis Table and have created a copy of the table with the "Filter" prefix before Table and Column names as in figure two,

Then i created the Orders measure - distcount of the OrderID - Calculated Correctly then on the 2nd measure i just get an error to use "USERELATIONSHIP" there has to be a relationship between the two tables, but when i try to create the relationship i get an error there has to be a unique identifier.

also if i remove the USERELATIONSHIP part from the code i just get a number back from the measure the same as the distcount.

Killing me now, as it would be ideal. & altogeth i can filter the seperated table thnis seems faster and more edaptable for down the line...

Any helps appreciated !!

Cheers,

J

[Anonymous]

@OwenAuger 

Hi Owen,

How would this translate into PowerBI though as its focusing on Pivot Tables?

Would i just create measures like:

[Orders with Both Products] :=
CALCULATE (
    DISTINCTCOUNT ( Sales[SalesOrderNumber] ),
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[SalesOrderNumber] ),
        ALL ( Product ),
        USERELATIONSHIP ( Sales[ProductCode], 'Filter Product'[Filter ProductCode] )
    )
)

But how does the SUMMARIZE funtion work in a measure?

Then afterwards how would you use this information? as a measure normally returns a single output whereas where wanting an adaptable table or some form of visual to show a single product sold X ammount of times with multiple other products and the count of occurence?

Also this uses the calculate table? would that not return a table? How would that work in a measure?

Thanks,

J

[Anonymous]

Sorry for the delay investigating this solution tonight will let you know what i think,

Thanks for the help

Cheers,

J