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

GetOnly orders with 1 commodity in a multi OLAP_no modelling , can't create any calculated columns

I need to get only orders with 1 commodity in a multi OLAP (no modelling , can't create any calculated columns)

 

The basic code that I thought:

 

AS IS
/*------------------------------------*/
define var tmdr =
CALCULATETABLE (
 
SUMMARIZECOLUMNS (
'Fact - Order Item Ordered'[Order Number],
FILTER (
'Fact - Order Item Ordered',
'Fact - Order Item Ordered'[Order Number] <> BLANK ()
&& DISTINCTCOUNT ( 'Hier - Commodity'[Commodity Descrip] ) = 1
),
"One Commodity", DISTINCTCOUNT ( 'Hier - Commodity'[Commodity Descrip] ) )
 
, 'Fact - Order Item Ordered'[Order Number] in {"0888889448","08888823824"}
)
evaluate sample(11, tmdr, 1);
 

 

 

 

Where I am looking to replace this two >>> DISTINCTCOUNT ( 'Hier - Commodity'[Commodity Descrip] )
with TREATAS function because I am in Multi OLAP.

 

Here is the virtual relationship with treatas


CALCULATE(
DISTINCTCOUNT('Hier - Commodity'[Commodity Descrip])
, TREATAS (
CALCULATETABLE(VALUES ( 'Fact - Order Item Ordered'[Item Number] )),
'Hier - Commodity'[Item Number]
)

The first highlight in light green need to be a number (b/c I need to get a number that allow to do the FILTERING)
Then in the second DISTINCTCOUNT I can replace directly straight witout  any change with the TREATAS.

 

the result that I need>>

Order number                        One Commodity
08888823824                                    6                                         No coming
08888289448                                    1                                       I need only this one

 

 

But I dont see   in the first distinctcount how to do Fitler where inside iis the TREATAS.

 

thanks in advacne for the help

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: GetOnly orders with 1 commodity in a multi OLAP_no modelling , can't create any calculated colum

Hi @perezco ,

 

I tried to create a sample to reproduce your scenario. But i failed. Can you please post some sample data in which you can mask the sensitive data. And can you share more details about the relationship among the tables? Do you must use the function of treatas? Maybe it will be solved by the other function.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate II
Advocate II

Re: GetOnly orders with 1 commodity in a multi OLAP_no modelling , can't create any calculated colum

The tables 'Fact - Order Item Ordered' and 'Hier - Commodity' does not have any connection.

The treatas is the only function that  I see I can use to connect these two table virtually by using the Item_Number to allow identify the orders that has one commodity.

 

Recreate the OLAP will take me a while ...because I  do not have acess to the model relation

 

Highlighted
Advocate II
Advocate II

Re: GetOnly orders with 1 commodity in a multi OLAP_no modelling , can't create any calculated colum

Question - Does exists a limitation to try use TREATAS inside of the filter function?

 

update:  I still not be able resolve this... 

 

Highlighted
Community Support
Community Support

Re: GetOnly orders with 1 commodity in a multi OLAP_no modelling , can't create any calculated colum

Hi @perezco ,

Apologies for late reply.

You could define an arbitrary shaped filter using the function of TREATAS.  And there is a blog you can learn about it.

If you still can't solve the problem, maybe you could post a dummy file or some sample data and expected output. Then we can help you as soon as possible.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors