cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
perezco Advocate I
Advocate I

different commnad that is not FILTER by using CALCULATETABLE

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

3 REPLIES 3
Community Support
Community Support

Re: different commnad that is not FILTER by using CALCULATETABLE

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
perezco Advocate I
Advocate I

Re: different commnad that is not FILTER by using CALCULATETABLE

Thanks for the article ,

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 Advocate I
Advocate I

Re: different commnad that is not FILTER by using CALCULATETABLE

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?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors