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 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
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
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:

Create a new table:

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!

12 Replies

BeaBF
Super User
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
- SagarJagga
  Frequent Visitor
  2 years ago
  can you please attach your pbix file
  - BeaBF
    Super User
    2 years ago
    SagarJagga Here the link:
    https://drive.google.com/file/d/1Rp1ZT4XBQIVEO-6VnrzWFurSSayMPkK4/view?usp=drivesdk
    
    BBF
- SagarJagga
  Frequent Visitor
  2 years ago
  Hi BeaBF ,
  Why we are using different table for Name and using ALL() in segment calculation
  - BeaBF
    Super 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
Anonymous
Not applicable
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:

Create a new table:

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!

Forum Discussion

Need help with DAX