Forum Discussion

SagarJagga's avatar
SagarJagga
Frequent Visitor
2 years ago
Solved

Need help with DAX

Hi All, I need your help in framing a DAX. Below is the sample data - Name Metro Segment Sales A New MID 55 A Columbo MID 60 B Other MID 100 C Ind Large 200   I ...
  • BeaBF's avatar
    BeaBF
    2 years ago

    SagarJagga Hi!

     

    First, you need to have a distinct separate table for "Name", you can calculate the table with this formula:

    NameTable = DISTINCT('Table'[Name])

    Then, create a two measures:
    SelectedSegment = VAR selitem = SELECTEDVALUE(NameTable[Name])
    RETURN
    CALCULATE(
        MAX('Table'[Segment]),
        'Table'[Name] = selitem,
        ALL()
    )
    FilteredSales =
    VAR SegmentToFilter = [SelectedSegment]
    RETURN
    CALCULATE(
        SUM('Table'[Sales]),
        'Table'[Segment] = SegmentToFilter
    )
     
    and put in the matrix "Metro" and this last measure. You'll obtain:

    If it's ok, please accept this answear as a solution.

     

    BBF



  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SagarJagga ,

     

    Your solution is great, BeaBF . Here I have another idea in mind, and I would like to share it for reference.

     

    The steps are as follows:

     

    1. Create a new table:

     

    1. Create a measure, the formula is as follows:

     

    Measure =
VAR _my_se = VALUES('Table 2'[Table_Segment])
RETURN
CONCATENATEX(FILTER('Table','Table'[Segment] IN _my_se),1)​

     

    • Filtering operations on measure:

    • The result is shown in the figure with a slicer ,Table and Table2.

       

     

     

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

SagarJagga because we need to remove the native interaction between Name and pass only the filter on Segment, to obtain the three rows set to the same Segment of the Name selected. 

 

BBF

SagarJagga's avatar
SagarJagga
Frequent Visitor
2 years ago

thanks for the explantion but why all is used. using seperate table to remove the interaction i understood

 

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    SagarJagga for the same reason, because Name and Segment are in the same table, you need to "sfilter" the selection on Segment from the Name row context. 

    In summary, ALL() is used to ensure that the MAX('Table'[Segment]) function considers all possible values in the Segment column of the Table, regardless of filters active in the context.

     

    BBF