Forum Discussion

abhiram342's avatar
abhiram342
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Help on Treatas DAX Code

Hi All,

 

I want to create measure for below scenario but not working as expected. Can someone please assist?

 

Scenario:

I want to get distinct Products with either no internet sales or Offline sales

EX:

In Internet sales: 2,4 ( productID)

In Offline Sales :2,3 (ProductID)

Final Measure: 2,3,4 ( Unique product from Internet and Offline Sales

DimProduct:

ProductIdProductName
1Bike
2Car
3Bi-Cycles

FactInternetSales

ProductIdSalesAmount
110
20
350
40

FactOfflineSales

ProductIdSalesAmount
110
20
30
410

 

Thanks,

Abhiram

DAX

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    abhiram342 , Create a measure like

     

    new measure =Sum(FactInternetSales[SalesAmount]) + Sum(FactOfflineSales[SalesAmount])

     

    Then create this measure 

    check measure =
    calculate( if( isblank([new measure]),1,blank()))

     

    or


    check measure =
    countx(values(DimProduct[ProductId] ) if( isblank([new measure]),[ProductId],blank()))

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

    Hi, abhiram342 

    Please correct me if I wrongly understood your question.

    I assume you want to create a new table.

    please try the below for creating a new table.

     

     

    new table =
    DISTINCT (
    UNION (
    SUMMARIZE (
    FILTER ( InternetSales, InternetSales[SalesAmount] = 0 ),
    InternetSales[ProductId]
    ),
    SUMMARIZE (
    FILTER ( OfflineSales, OfflineSales[SalesAmount] = 0 ),
    OfflineSales[ProductId]
    )
    )
    )
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM