Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |