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

Question

I need to get only orders with 1 commodity in a multi OLAP (no modelling , can't create any calculated columns)&nbsp;The basic code that I thought:&nbsp;AS IS/*------------------------------------*/define var tmdr =CALCULATETABLE (&nbsp;SUMMARIZECOLUMNS ('Fact - Order Item Ordered'[Order Number],FILTER ('Fact - Order Item Ordered','Fact - Order Item Ordered'[Order Number] &lt;&gt; BLANK ()&amp;&amp; DISTINCTCOUNT ( 'Hier - Commodity'[Commodity Descrip] ) = 1),"One Commodity", DISTINCTCOUNT ( 'Hier - Commodity'[Commodity Descrip] ) )&nbsp;, 'Fact - Order Item Ordered'[Order Number] in {"0888889448","08888823824"})evaluate sample(11, tmdr, 1);&nbsp;&nbsp;&nbsp;&nbsp;Where I am looking to replace this two &gt;&gt;&gt; DISTINCTCOUNT ( 'Hier - Commodity'[Commodity Descrip] )with TREATAS function because I am in Multi OLAP.&nbsp;Here is the virtual relationship with treatasCALCULATE(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&nbsp; any change with the TREATAS.&nbsp;the result that I need&gt;&gt;Order number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; One Commodity08888823824&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; No coming08888289448&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I need only this one&nbsp;&nbsp;But I dont see &nbsp; in the first distinctcount how to do Fitler where inside iis the TREATAS.&nbsp;thanks in advacne for the help

v-xuding-msft · Answer

Hi&nbsp;perezco&nbsp;,&nbsp;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&nbsp;treatas? Maybe it will be solved by the other function.&nbsp;Best Regards,Xue DingIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;&nbsp;

perezco · Answer

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

perezco · Answer

Question - Does exists a limitation to try use TREATAS inside of the filter function?&nbsp;update:&nbsp; I still not be able resolve this...&nbsp;&nbsp;

v-xuding-msft · Answer

Hi&nbsp;perezco&nbsp;,Apologies for late reply.You could define an arbitrary shaped filter using the function of TREATAS.&nbsp; 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.&nbsp;Best Regards,Xue DingIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

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

4 Replies

Related content

Direct Lake Model - Calculated Column/Tables

Directlake semantic model, calculated table

Measure/Model calculation support needed

Calculate Cost of Commodity with DAX

Modelling