Forum Discussion

ankababu007's avatar
ankababu007
Frequent Visitor
3 years ago
Solved

Need help with Dax

I have below data set

CustomerSourceSalesAmt
cust-1OMS500
cust-2OMS100
cust-3OMS50
cust-4OMS100
cust-1G5100
cust-2G5200
cust-4G5200
cust-2Onsite300
cust-4Onsite100

 

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 count1
Sales Amt600
  
Example-2 
Select OMS
 G5
 OnSIte
Result 
cust count2
Sales Amt1000
  
Example-1 
Select OMS
  
Result 
cust count1
Sales Amt50

 

Need Help

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

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