Forum Discussion
Sum (SKUs in two columns)
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.
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.
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:
and here is sample pbix file, please try it.
Regards,
Lin
9 Replies
v-lili6-msftCommunity 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.
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:
and here is sample pbix file, please try it.
Regards,
Lin
- VijayP
Community Champion
Anonymous
Try creating a custom column in Power Query editor with Result you want!
Let me know that is doable
Regards
Vijay Perepa
