Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
@Anonymous
Try creating a custom column in Power Query editor with Result you want!
Let me know that is doable
Regards
Vijay Perepa
Proud to be a Super User!
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)
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |