Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create a custom Table

Hello All,

 

Please me with a DAx to create a new Table with an existing DATASET with Filter.

I need to carete a New table With columns Products and Amounts with Blanks. Result 

 

 

REgards,

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    Anonymous try this DAX formula, I blindly followed what you explained.

     

    Result = 
VAR __table = SUMMARIZE( SetB, SetB[Product], SetB[Region], "Sales", SUM( SetB[Sales] ) ) 
RETURN
UNION(
INTERSECT( SetA, SUMMARIZE( FILTER( __table, [Sales]= 0 ), [Product], [Region] )),
EXCEPT( SUMMARIZE( FILTER( __table, [Sales]= 0 ), [Product], [Region] ), SetA )
)

    here is the output of result table

8 Replies

  • sktneer's avatar
    sktneer
    Resolver I
    7 years ago

    Assuming the name of the Table is Sales, then you can create a New Table like below...

     

    Sales Table With Zero Amount 
= FILTER(
    Sales,
    Sales[Amount]=0
)

     

    Edit:

    And if you just want to have only two columns Product and Amount in the New Table, try it like this...

     

    Products With Zero Sales
= FILTER(
    ALL(
        Sales[Product],
        Sales[Amount]
        ),
    Sales[Amount]=0
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      Let me explain in detail about the requirement.

      I have 2 datasets A & B A is primary Set9 Unique Values and B is a Normal data. I need to find the odd values from B by comparing the values with SetA.

      Exap:

      1.The Result is to get the Region with Zero sales and the region should be in SETA.

      Exaple, Product A with Sales 0 In Florida should be excluded. only Products with Region exists in SET A should apprear.

      Only Zero sales Region with product should be tracked.

      Please check the clienk for your reference.

      https://drive.google.com/file/d/1JhAEHlYQGAJUaJ7v3KrSZbLFLt6lC3Yx/view?usp=sharing

       

      Please let me know if you need further details,

       

      REgards,

       

       

      Regards.

      • sktneer's avatar
        sktneer
        Resolver I
        7 years ago

        That's a completely different requirement altogether.

        Look at your first two posts, you also provided a sample dataset in your second post and none of those two posts had any clue about this requirement at all.

        What's the point in not adding that detail in your original post at the first place?

  • Naveen_SV's avatar
    Naveen_SV
    Helper IV
    7 years ago

    Hi ,

     

    you can try out in the below way.

    Try to create a column where you can assign which has value or Null Value in Table 1

    Column = IF(Table1[Amount]=BLANK(),0,Table1[Amount])
    Then in your table 2 Create a unique (or distinct) "product" Values. Create a measure 
    Measure = CALCULATE(SUM(Table1[Column]),IF(Table1[Column]=0,1,0),CROSSFILTER(Table1[Product],Table2[Product],Both))
    you will be able to saperate product which is showing 0 or Null.