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

Need help with Dax

ankababu007 — Fri, 24 Mar 2023 17:32:35 GMT

I have below data set

Customer	Source	SalesAmt
cust-1	OMS	500
cust-2	OMS	100
cust-3	OMS	50
cust-4	OMS	100
cust-1	G5	100
cust-2	G5	200
cust-4	G5	200
cust-2	Onsite	300
cust-4	Onsite	100

I have a slicer by source when I select OMS i should only show cust-1 data as that is the only customer who has only OMS . If we select OMS and G5 then we should show data for cust-1,cust-2,cust-4. based on selection for source we should only display customers that belong to only those sources.

This is what the out put I am expecting. Please some one can help in writing a dax for this that will be great

Example-1
Select	OMS
	G5
Result
cust count	1
Sales Amt	600

Example-2
Select	OMS
	G5
	OnSIte
Result
cust count	2
Sales Amt	1000

Example-1
Select	OMS

Result
cust count	1
Sales Amt	50

Re: Need help with Dax

Jihwan_Kim — Sat, 25 Mar 2023 04:36:11 GMT

Hi,

Please check the below picture and the attached pbix file.

Customer count: = VAR _sourcecount = COUNTROWS ( DISTINCT ( Source[Source] ) ) VAR _selectedsource = DISTINCT ( Source[Source] ) VAR _nonselectedsource = EXCEPT ( ALL ( Source[Source] ), _selectedsource ) VAR _customerstable = ADDCOLUMNS ( Data, "@sourcecount", COUNTROWS ( FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) ) ) ) VAR _customerlist = SUMMARIZE ( FILTER ( _customerstable, [@sourcecount] = _sourcecount ), Data[Customer] ) VAR _exceptcustomerlist = SUMMARIZE ( FILTER ( ALL ( Data ), Data[Source] IN _nonselectedsource ), Data[Customer] ) VAR _expectedcustomerlist = EXCEPT ( _customerlist, _exceptcustomerlist ) RETURN COUNTROWS ( _expectedcustomerlist )

Customer sales expected result: = VAR _sourcecount = COUNTROWS ( DISTINCT ( Source[Source] ) ) VAR _selectedsource = DISTINCT ( Source[Source] ) VAR _nonselectedsource = EXCEPT ( ALL ( Source[Source] ), _selectedsource ) VAR _customerstable = ADDCOLUMNS ( Data, "@sourcecount", COUNTROWS ( FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) ) ) ) VAR _customerlist = SUMMARIZE ( FILTER ( _customerstable, [@sourcecount] = _sourcecount ), Data[Customer] ) VAR _exceptcustomerlist = SUMMARIZE ( FILTER ( ALL ( Data ), Data[Source] IN _nonselectedsource ), Data[Customer] ) VAR _expectedcustomerlist = EXCEPT ( _customerlist, _exceptcustomerlist ) RETURN SUMX ( FILTER ( Data, Data[Customer] IN _expectedcustomerlist ), Data[SalesAmt] )

Re: Need help with Dax

ankababu007 — Sat, 25 Mar 2023 05:29:58 GMT

Hi Thank you very much for your solution I think when I am displaying in Matrix its not working as intended to.

In this image if I select OMS and G5 matrix should show cust-1 with OMS value of 500 and G5 value of 100.

Re: Need help with Dax

ankababu007 — Sat, 25 Mar 2023 05:40:37 GMT

Please find the url for pbix file.I want to see that data in Matrix..

https://drive.google.com/file/d/1pHHGbbo-3eaNA-zB_Lld8QN9H08qS1ZZ/view?usp=sharing

Re: Need help with Dax

Jihwan_Kim — Sun, 26 Mar 2023 05:46:07 GMT