different commnad that is not FILTER by using CALCULATETABLE

[perezco]

Good Evening,

I have to capture the orders that have  a single product inside of the order.

Doe exist a different commnad that is not FILTER to be filter single Item_Number?
the tables have connection with the Dps_Number in both tables.

note:this under olap ... so I don't have option to do any addition of custom column

DEFINE
VAR tmdr =
CALCULATETABLE (
    'Fact - D_Item_Ordered',

    'Fact - D_Item_Ordered'[Item_Nbr],
    'Fact - D_Item_Ordered'[Dps_Number]
        IN { "6666710474", "6666730547", "666848040" },
    FILTER (
        'Fact - D_Item_Ordered',
        CALCULATE ( DISTINCTCOUNT ( 'Fact - D_Item_Ordered'[Item_Nbr] ) ) = 1
    )
)
EVALUATE
SAMPLE ( 11, tmdr, 1 );

thanks in advance...

[v-shex-msft]

HI @perezco ,

You can refer to following link to use all function to ignore specific filter effect:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

Regards,

Xiaoxin Sheng

[perezco]

Thanks for the article v-shex-msft,

I will look this later.  I reviewed  it...

But right now,

I am looking these set of dax commands:

                                      CALCULATETABLE, SUMMARIZECOLUMNS, DISTINCTCOUNT, FILTER.

Even with these set of commands ...

After the command SUMMARIZECOLUMNS... I still not be able to use inside of CALCULATETABLE:
FILTER (
        'Fact - D_Item_Ordered',
        CALCULATE ( DISTINCTCOUNT ( 'Fact - D_Item_Ordered'[Item_Nbr] ) ) = 1
    )

Now  just adding command ALL  inside of FILTER  is not giving the error.. but I still not be able to FILTER.

define var tmdr =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Fact - D_Item_Ordered'[Dps_Number],
////'Fact - D_Item_Ordered'[Item_Nbr],
"Amount distinct", DISTINCTCOUNT ( 'Fact - D_Item_Ordered'[Item_Nbr] )
)
,'Fact - D_Item_Ordered'[Dps_Number]
IN { "6666710474", "6666730547", "666848040" }

,FILTER (ALL( 'Fact - D_Item_Ordered'[Item_Nbr] ),CALCULATE ( DISTINCTCOUNT ( 'Fact - D_Item_Ordered'[Item_Nbr] ) ) = 1 )

)
EVALUATE SAMPLE ( 11, tmdr, 1 );

Then I thought that  I can create a variable table1 and in the second tbl2 only filter those with the amount of one product.

define
VAR Table1=
SUMMARIZECOLUMNS(
'Fact - D_Item_Ordered'[Dps_Number]
////'Fact - D_Item_Ordered'[Item_Nbr],
, FILTER('Fact - D_Item_Ordered', [Dps_Number] IN {"6666710474", "6666730547", "666848040" })
, "Amount distinct", DISTINCTCOUNT ('Fact - D_Item_Ordered'[Item_Nbr] )
)

var tbl2=CALCULATETABLE(
Table1,
FILTER(Table1, [Amount distinct]=1)
// FILTER ( Table1 ,CALCULATE ( Table1, [Amount distinct]=1 ) )

)
evaluate tbl2

Maybe  I am asking to much but Does this can be done under calculation measure? I don't option to make column calculation   b/c I am under a OLAP structure.

[perezco]

t=
CALCULATETABLE (
VALUES ( 'Hier - Commodity'[Commodity Description] ),
TREATAS (
CALCULATETABLE ( VALUES ('Fact - D_Item_Ordered'[Item_Nbr] ) ),
'Hier - Commodity'[Item_Nbr]
)
)
///I GET the value from the target column called Commodity Description

But,  How Do  I add the columns from 'Fact - D_Item_Ordered' by using treatas?