Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone Here,
I'm hoping someone is able to help me solve a few questions on a Data Model I'm working on which are Mutually Exclusive 🙂
Basically here are the conditions;
1) Each Customer Segmentation can only buy only 1 Item Segmentation that is assigned to them
2) If Item Code Classification contained both Set & Single then Set is split by 50% and Single is 100% each
2a) If Item Code Classification contained Set then is 100%
2b) If Item Code Classification is Single then is 100% for both
Things I'have done within PowerQuery;
These are the current problem I'm facing;
(I've Created a Rule in Rule Tab and merge it to the main table, thus it is bringing in 50%)
I can't do this part as I can't find a method to lookup via the column
I've the excel file with the desire outcome, i can send the file over as i cant attached file here 🙂
appreciate any help here 🙂
Hi @Rookarumba,
Please create calculated columns via DAX
Population split via Item Segmentation = IF ( CALCULATE ( DISTINCTCOUNT ( Test_1[Item Code Classification] ), ALLEXCEPT ( Test_1, Test_1[Customer Segmentation], Test_1[Item Segmentation] ) ) > 1, Test_1[Population] * 0.5, Test_1[Population] * 1 ) Population split via Item Code Classification = Test_1[Population split via Item Segmentation] / CALCULATE ( DISTINCTCOUNT ( Test_1[Item Code Classification] ), ALLEXCEPT ( Test_1, Test_1[Customer Segmentation], Test_1[Item Segmentation] ) ) / CALCULATE ( DISTINCTCOUNT ( Test_1[Item Code] ), ALLEXCEPT ( Test_1, Test_1[Customer Segmentation], Test_1[Item Segmentation], Test_1[Item Code Classification] ) )
Best regards,
Yuliana Gu
@v-yulgu-msft, Thanks for the solution but I might have oversimplified my original question.
I've print screen another scenario, whereby there a total of 8 rows and 4 sets.
I try to troubleshoot and found out that;
Population split via Item Segmentation = IF ( CALCULATE ( DISTINCTCOUNT ( Test_1[Item Code Classification] ), ALLEXCEPT ( Test_1, Test_1[Customer Segmentation], Test_1[Item Segmentation] ) )
Give me a count of 2, instead of 4.
I tried to run it in PowerQuery too, via Groupby "Customer Segmentation", "Item Segmentation", "Population", and distinct count rows.
And I got a count of 2 instead of 4 too.
The rest seems fine except for this part, I can't figure it out whats going on...
Appreciate it again 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |