Forum Discussion
ankababu007
3 years agoFrequent Visitor
Need help with Dax
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 ...
- 3 years ago
Jihwan_Kim
Super User
3 years agoHi,
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
3 years agoFrequent Visitor
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.