Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum (SKUs in two columns)

1.PNG 

Background: 

Data table: EventID (Primekey) | Machines | Date/Time | Stage1 | Stage 2
Dim table: SKU Name | SKU ID 

 

Problem need help with: 

without having to unpivot the whole data table.  How can i find out the usage of each SKUs? (like the following) 

 

Ps: For every event, it could be a 1 or 2 stage , and any SKU can be used in either stages. 

2.PNG

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

If you don't want to unpivot the whole data table. you could try this way as below:

Create two inactive relationship between data table and dim table.

1.JPG

 

Then create separate measure by userrelationship function as below:

Stage1 = CALCULATE(COUNTA('Data table'[Stage1]),USERELATIONSHIP('Dim table'[SKU Name],'Data table'[Stage1]),'Data table'[Stage1]<>BLANK())

Stage2 = CALCULATE(COUNTA('Data table'[Stage2]),USERELATIONSHIP('Dim table'[SKU Name],'Data table'[Stage2]),FILTER('Data table','Data table'[Stage2]<>BLANK()))

Total = [Stage1]+[Stage2]

Result:

2.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

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

View solution in original post

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

If you don't want to unpivot the whole data table. you could try this way as below:

Create two inactive relationship between data table and dim table.

1.JPG

 

Then create separate measure by userrelationship function as below:

Stage1 = CALCULATE(COUNTA('Data table'[Stage1]),USERELATIONSHIP('Dim table'[SKU Name],'Data table'[Stage1]),'Data table'[Stage1]<>BLANK())

Stage2 = CALCULATE(COUNTA('Data table'[Stage2]),USERELATIONSHIP('Dim table'[SKU Name],'Data table'[Stage2]),FILTER('Data table','Data table'[Stage2]<>BLANK()))

Total = [Stage1]+[Stage2]

Result:

2.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
VijayP
Super User
Super User

@Anonymous 

Try creating a custom column in Power Query editor with Result you want!

Let me know that is doable

Regards

Vijay Perepa




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@VijayP 

adding a column in the dim_product table ? how do i count Stage 1 and Stage 2 and the combo of them in the data table?

@Anonymous  The way you have shown in the second picture. Else i need you to elaborate your question (rephrase your question)




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

@VijayP 

 

So 1st pic is the data table. 

How would you find out How many SKU is used overall for each SKU?  

@Anonymous 

Then you need to create another Column with an indicator showing whether 1 SKU used or 2 SKUs used (based on the data you have shown)

Using DAX we can find out whehter both SKUs used or only One SKUs used based on the additional Column mentioned above using Calculate and FIlter




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

Right... But i need help with the Dax lol 

@Anonymous 

Is is possible to sahre smalle piece of data so that i can give you the better solution

vijay Perepa




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.