Need help with DAX

[SagarJagga]

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 want to create a table by using Metro and Sum of Sales; Name will be in the filter.

Let me explain you the requirement with example. Let's say A Name has been selected from the filter. The corresponding segment is MID so in table, all the Metros corresponding to MID should be shown in the table i.e New, Columbo and Other. 

Regards,

Sagar

[BeaBF · Accepted solution]

@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 · Accepted solution]

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 · Accepted solution]

@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

[SagarJagga]

Hi @BeaBF ,

Why we are using different table for Name and using ALL() in segment calculation

[BeaBF]

@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]

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

[BeaBF]

@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

[SagarJagga]

can you please attach your pbix file

[BeaBF]

@SagarJagga Here the link:

https://drive.google.com/file/d/1Rp1ZT4XBQIVEO-6VnrzWFurSSayMPkK4/view?usp=drivesdk

BBF

[SagarJagga]

Not sure why it is not working for me. i have tried by passing hard code value for Segment as well 

[BeaBF]

@SagarJagga in the filter you have to use Name of Tablename, see my pbix

[SagarJagga]

i can not open google drive due to limitations. can you please attached the file here

[BeaBF]

@SagarJagga i've sent it to your mail. Please let me know if it's ok and accept the solution.

BBF