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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
oscarca
Helper I
Helper I

How to use a measure as a filter in another measure ?

Hello community!

I am trying to create a measure that calculates the total product sales for a specfic month only for does products that has been sold the same period last year. 


The first thing I did was to create a measure to calculate the Sales Amount for previous year:
Sales Amount PY =
CALCULATE([Sales Amount],SAMEPERIODLASTYEAR(DimDate[Datekey]))


The second thing I did was to create a Comparable range flag as measure:

ComparableRange = IF(FactSales[Sales Amount] = BLANK() || FactSales[Sales Amount PY] = BLANK(),0,1)

Third step I created a measure to calculate the total product sales:
Total Product Sales =
CALCULATE(FactSales[Sales Amount],ALL(DimProduct))

The final step I want to calculate the total product sales only for does products being comparable.
I tried this solution but not getting it to work, it is only returning blank:
Total Product Sales Comparable =
var CompRangeTable = ADDCOLUMNS(FactSales,"@CompRange",[ComparableRange])
var FilteredTable = FILTER(CompRangeTable,[@CompRange] = 1)
return
CALCULATE(FactSales[Sales Amount],ALL(DimProduct),FilteredTable)
 
oscarca_0-1671183478562.png

I also tried this solution but still getting blanks:
Total Product Sales Comparable =
var FilteredTable = FILTER(FactSales, [Sales Amount PY]*[Sales Amount]+0<>0)
return
CALCULATE([Sales Amount],ALL(DimProduct),FilteredTable)

To demonstrate my problem I have used the ContosoRetailDW sample database with a simple star scheme consisting in the tables "FactSales", "DimDate" and "DimProduct"

oscarca_1-1671183760733.png


Thanks in advanced for any help.









1 ACCEPTED SOLUTION

I re-wrote the solution to the following:

sumx(filter(All(DimProduct[ProductName]),[Sales Amount PY]*[Sales Amount]+0<>0),FactSales[Sales Amount])
Which produces the desired result and with less code.

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

A measure is returning a scalar value. A filter expects a table. Rewrite your second measure to incorporate the same logic as you did in the first.

I adjusted the measure to create the CompRangeTable based on DimProduct instead of the fact table:

Total Product Sales Comparable v1 =
var CompRangeTable = ADDCOLUMNS(VALUES(DimProduct[ProductName]),"@CompRange",[ComparableRange])
var FilteredTable = FILTER(CompRangeTable,[@CompRange] = 1)
return
CALCULATE(FactSales[Sales Amount],ALL(DimProduct),FilteredTable)

And it is able to display the Sales Amount that is comparable. The only issue is that is seems to ignore the ALL(DimProduct) in the return statement. So it only returns the same as the "Sales Amount"
oscarca_0-1671373007024.png

It should return 13,811,177 on each row which is the total:

oscarca_1-1671373250573.png

 

Use a step by step approach with variables and use EVALUATEANDLOG to ensure each step is producing the desired result.

I re-wrote the solution to the following:

sumx(filter(All(DimProduct[ProductName]),[Sales Amount PY]*[Sales Amount]+0<>0),FactSales[Sales Amount])
Which produces the desired result and with less code.

Thank you for you respons Ibendlin!
When you say the second measure, do you mean I should adjust this one ?

Total Product Sales Comparable =
var FilteredTable = FILTER(FactSales, [Sales Amount PY]*[Sales Amount]+0<>0)
return
CALCULATE([Sales Amount],ALL(DimProduct),FilteredTable)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.