Forum Discussion

seba's avatar
seba
Helper II
2 years ago
Solved

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”...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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
SatisfiedCount
Best Fruit Mix = 
VAR FruitCombinations = SUMMARIZE('Table', [Fruit])
VAR BestMix = 
    TOPN(
        1,
        FruitCombinations,
        [Satisfied People],
        DESC
    )
RETURN
BestMix

 

 

 

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.

  • seba's avatar
    seba
    Helper II
    2 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.