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      ...
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

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.