Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I have a calculated table. It show the amount of sales by type of price. I do not know how to leave rows with values in this calculated table and delete empty ones. Please, help. Maybe someone knows how to do it
Amid,
The trick is to create a relationship that links the 2 tables you have for sales and for price by date. The best option here is to relate them by the date. Then you can write a simple measure that just takes the number of sales multiplied by the price of the object on that given date.
https://drive.google.com/file/d/1rhZBL6K7fH5YwcJqY6_w82b4dSP0xwtC/view?usp=sharing
@Anonymous , this is a good solution, but not enough for my case. My problem is to reduce the size of the calculated table Prices per dates. Now her DAX code is as follows:
Prices per dates =
VAR t =
FILTER (
CROSSJOIN (
GROUPBY ( 'Prices', Prices[Product_id], Prices[Date_from], Prices[Date_till], Prices[Price$], Prices[Price_type] ),
'Dates'
),
'Dates'[Date] >= 'Prices'[Date_from]
&& 'Dates'[Date] < 'Prices'[Date_till]
)
RETURN t
But this DAX code now returns an excess of lines. Now my main task, which I can’t solve yet, is to remove these lines at the level of that DAX code.
As a result, the calculated table Prices per dates should not have all the dates from the date range in the Prices table, but only those that are in the Sales table.
I already have a formula for getting the sales amount, it works well. The problem is in the unnecessary rows of the calculated table Prices per dates.
Sales Amount per Prices =
CALCULATE (
[Amount],
TREATAS (
SUMMARIZE (
'Prices per dates',
'Prices per dates'[Date],
'Prices per dates'[Product_id]
),
Sales[Date],
Sales[Product_id]
)
)
* AVERAGE ( 'Prices per dates'[Price$] )
clarification
it is necessary that the number of unique combinations of Product_id Date in Sales and Prices per dates be the same. In the example, it should be 35
http://prntscr.com/ql5qm5
I try to continue the DAX formula with TREATAS to create a virtual relationship.
And TREATAS returns the correct result 35 rows
But then, something goes wrong ... 698 rows
The problem turned out to be solved, but not sure about the speed on a large amount of data ..
The question remains open. Can someone tell me another solution
https://prnt.sc/ql6h0t
🤔 very very slow ... need to look for another solution ..
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |