topic Re: Need help with DAX in DAX Commands and Tips

Need help with DAX

SagarJagga — Tue, 09 Jul 2024 08:02:02 GMT

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

Re: Need help with DAX

BeaBF — Tue, 09 Jul 2024 08:42:03 GMT

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

Re: Need help with DAX

SagarJagga — Tue, 09 Jul 2024 09:02:58 GMT

can you please attach your pbix file

Re: Need help with DAX

BeaBF — Tue, 09 Jul 2024 09:17:54 GMT

SagarJagga Here the link:

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

BBF

Re: Need help with DAX

SagarJagga — Tue, 09 Jul 2024 09:21:47 GMT

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

Re: Need help with DAX

BeaBF — Tue, 09 Jul 2024 09:23:14 GMT

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

Re: Need help with DAX

SagarJagga — Tue, 09 Jul 2024 09:28:06 GMT

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

Re: Need help with DAX

Anonymous — Tue, 09 Jul 2024 10:00:40 GMT

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!

Re: Need help with DAX

BeaBF — Tue, 09 Jul 2024 10:02:34 GMT

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

BBF

Re: Need help with DAX

SagarJagga — Tue, 09 Jul 2024 15:11:59 GMT

Hi BeaBF ,

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

Re: Need help with DAX

BeaBF — Tue, 09 Jul 2024 15:13:54 GMT

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

Re: Need help with DAX

SagarJagga — Tue, 09 Jul 2024 15:21:21 GMT

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

Re: Need help with DAX

BeaBF — Tue, 09 Jul 2024 15:30:06 GMT

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