Forum Discussion
Power Participantmeasure with two filter functions
I tried to writer a measure which allows me to filter data from two tables (and condition between two filters). For some reason, the measure give blank() results while there should be some amounts calculated based on both filters.
measure=
5 Replies
- amitchandak
Super User
Jeanxyz , This seems fine. Check data type Approved in Time is boolean. Else change it "True".
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- Jeanxyz
It looks the yearmonth selection in the filter pane conflicts with the measure. Below is the sales raw data.
in the measure, I asked for amounts from day 1 till 2024-03-01, however, the measure only gives sales (300) from 2020-Jan because of the YearMonth selection in the filter pane. How can I ignore filter pane selection?
By the way, how can I attached a pbix file? I don't see the attachment option.
double filter =var test=calculate([sales],ALL(Dim_Date),filter(all(Dim_Date),Dim_Date[Date]<=date(2024,3,1)),filter(sales,sales[product category]="1A"))returntest- Jeanxyz
After lots of sweating, I start to figure out why the filters don't work.
As mentioned above, I have a simple sales table, the Date column in sales table is linked to Dim_Date table. Please notice that in 2020-02, there is no sales transactions reported in sales table.
In the visual below, I have selected YearMonth 2020-02 from the Dim_Date table. By default, both Dim_Date table and sales table are filtered by YearMonth 2020-02.
I want to calculate sales amoutn of product 1A from day 1 till 2022-01-01. As you can see [double filter3] doesn't work even if I have used removefilters() to remove any filter in Dim_Date, the reason is even though the filter in Dim_Date is removed, the filter on sales table still exists, sales table is filtered to YearMonth 2020-02. As there is no sales transaction in 2020-02, this measure generates no result.
In [double filter1], I have wrapped my sales filter expression with all(sales) to remove any filter on the sales table, this measure gives me the result as expected since YearMonth filter is removed from sales table.
However, there is still one thing confuses me. As you can see, I have created another measure called [double filter2]. In this measure, I have quoted another measure called [sales_product_1A]. This measure calculates sales amount of product 1A. This measure works as [double filter3]. But I don't quite understand the filtering procedure here. I would expect sales table still gets filtered by YearMonth 2020-01 in the filter pane. I was clearly wrong. Can someone explain difference between writing a filter function directly in a measure vs quoting another measure with the same filter function?
