Forum Discussion
Power BI Model Question
Hi! Hope all is well! Here is the challenge I am facing and looking for help with – I do have the below reference table with people names and fruits they would like to get. Each person is “satisfied” only when they get all the fruits they are looking for (for some it is one, for some it is multiple). I am looking to understand what code do I need to use in order to be able to answer the following questions (I believe they are all the same question just worded differently):
- How many people would be satisfied if I get:
- Just Apples
- Apples + Pears
- Just Oranges etc.
- Most critical question - all possible combinations (this can be accomplished using a filter or so), it would be great to automate it as much as possible instead of clicking different filter combinations and "writing down" the results
- What is a fruit mix which would satisfy the most people?
- Would love to be able to show how the number of satisfied people could grow as I add fruits to the mix
Any help would be greatly appreciated! Thank you!
Hi seba ,
I understand your requirement and I think you can change your table and use this DAX code. Here is a similar example.
Measure 3 = VAR _Slicer = VALUES('T2'[Car Name]) VAR _vtable = ADDCOLUMNS(GENERATE('T1',_Slicer),"AAA",IF(FIND('T2'[Car Name],'T1'[Name],,BLANK())<>BLANK(),'T2'[Car Name])) VAR _vtable2 = SUMMARIZE ( _vtable, [Name], "BBB", IF ( LEN ( CONCATENATEX ( _Slicer, 'T2'[Car Name] ) ) = LEN ( CONCATENATEX ( FILTER ( _vtable, 'T1'[Name] = EARLIER ( 'T1'[Name] ) ), [AAA] ) ), CONCATENATEX ( FILTER ( _vtable, 'T1'[Name] = EARLIER ( 'T1'[Name] ) ), [AAA] ) ) ) RETURN IF ( CONCATENATEX ( FILTER ( _vtable2, [Name] = 'T1'[Name] ), [BBB] ) <> BLANK (), CONCATENATEX ( FILTER ( _vtable2, [Name] = 'T1'[Name] ), [BBB] ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi seba
It sounds like you are talking about basket analysis.Please refer to the linked guides:
https://www.daxpatterns.com/basket-analysis/
https://blog.finance-bi.com/power-bi-basket-analysis/
and video:
https://www.youtube.com/watch?v=yzDGvZEtYdQ
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi seba ,
I create a table as you mentioned.
Then I create two measures and here is the DAX code.
Satisfied People = VAR SelectedFruits = ALLSELECTED('Table'[Fruit]) VAR SatisfiedCount = CALCULATE( DISTINCTCOUNT('Table'[Name]), FILTER( SUMMARIZE( 'Table', 'Table'[Name], "AllFruits", CALCULATETABLE(VALUES('Table'[Fruit]), ALL('Table')) ), ISEMPTY(EXCEPT(VALUES('Table'[Fruit]), SelectedFruits)) ) ) RETURN SatisfiedCountBest Fruit Mix = VAR FruitCombinations = SUMMARIZE('Table', [Fruit]) VAR BestMix = TOPN( 1, FruitCombinations, [Satisfied People], DESC ) RETURN BestMixBest Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for looking into this Anonymous ! I think we are close, but not there yet.
Using your code, when I filter for Orange, I see all people (Alice, Seb, Zach) who want orange as one of their fruits to be satisfied. However, I expect to see Zach only. The reason is only Zach wants only organges to be satisfied, he does not want any other fruits. Alice and Seb need more fruits to be satisfied (different fruits each one of them). A person is satisfied only when they get all the fruits they want (have listed on the spreadsheet).
That is how I would like to see the data - be able to manipulate, or better have Power BI tell me, fruits and see what mix of fruits will give me the highest number of satisfied people, again remebering that people are satisified only when they get all the fruits they want. Hope this clarifies my question.
Once again, thank you for help.
Hi seba ,
I understand your requirement and I think you can change your table and use this DAX code. Here is a similar example.
Measure 3 = VAR _Slicer = VALUES('T2'[Car Name]) VAR _vtable = ADDCOLUMNS(GENERATE('T1',_Slicer),"AAA",IF(FIND('T2'[Car Name],'T1'[Name],,BLANK())<>BLANK(),'T2'[Car Name])) VAR _vtable2 = SUMMARIZE ( _vtable, [Name], "BBB", IF ( LEN ( CONCATENATEX ( _Slicer, 'T2'[Car Name] ) ) = LEN ( CONCATENATEX ( FILTER ( _vtable, 'T1'[Name] = EARLIER ( 'T1'[Name] ) ), [AAA] ) ), CONCATENATEX ( FILTER ( _vtable, 'T1'[Name] = EARLIER ( 'T1'[Name] ) ), [AAA] ) ) ) RETURN IF ( CONCATENATEX ( FILTER ( _vtable2, [Name] = 'T1'[Name] ), [BBB] ) <> BLANK (), CONCATENATEX ( FILTER ( _vtable2, [Name] = 'T1'[Name] ), [BBB] ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
