Need help with Dax

[ankababu007]

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

[Jihwan_Kim]

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

[ankababu007]

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

[Jihwan_Kim · Accepted solution]

[ankababu007]

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.