Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
perezco
Advocate III
Advocate III

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
v-xuding-msft
Community Support
Community Support

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.

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

 

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

 

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.