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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
JesusPenelas
Frequent Visitor

Measure with a value filter calculated previously in a virtual table (generated with slicer filter)

Hello,

 

I have the following data model:

 

JesusPenelas_0-1669911832344.png

I need to create a measure that adds the sales from the FactSales table, but only when a store is "LFL", which is defined according to calculations to be carried out in the "FACT_LFL_STORE" table.

 

🚨  The logic to know if a store is LFL or not LFL is the following: For example, if in a slicer of my visualization I select 3 specific months, a store is considered LFL if for all those three selected months it has the value 1 in the "LFL_YN" column of the "FACT_LFL_STORE" table.

If a store has any of the months selected in the display slicer with "LFL_YN"=0 in the "FACT_LFL_STORE" table, then that store should not be counted in the sum of sales, even if there are some of the months selected in the slicer that have the field "LFL_YN"=1.

 

So, my problem is to get a measure that adds the sales when each store is considered as "LFL" according to my criteria in the period selected in the visualization slicer that uses the "MONTH_ID" field of the "DIMCALENDAR" table.

 

I have tried to build a virtual table that would summarize the "FACT_LFL_STORE" table, filtering those stores that according to the display slicer were "LFL", and then apply a filter to the sum of sales that would add only the sales of the stores that had in the previous virtual table, but I can't get it to work for me.

 

I would greatly appreciate if anyone knows how to fix this problem. 💛💛💛💛

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi  @JesusPenelas ,

 

Here are the steps you can follow:

1. Create measure.

Flag =
var _select=SELECTCOLUMNS('DimCalendar',"MonthID",[Month_ID])
var _table=
FILTER('Fact_LFL_Store','Fact_LFL_Store'[Month_ID] in _select && [LFL_YN]<>1)
var _column=SELECTCOLUMNS(_table,"Date",[STORE_ID])
return
IF(
   NOT( MAX('FactSales'[STORE_ID])) in _column && MAX('FactSales'[Month_ID]) in _select,1,0)
Sum_Measure =
SUMX(ALLSELECTED('FactSales'),[SALES])

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_0-1669964318565.png

3. Result:

Select 202208 202209 202210:

vyangliumsft_1-1669964318567.png

Select 202207 202208 202209:

vyangliumsft_2-1669964318569.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

2 REPLIES 2
JesusPenelas
Frequent Visitor

Thanks so much @Anonymous 

It's near from the result I need, but I Need use the "Flag" measure as a filter inside the "Sum_Measure".

Thank you in advance for your help 💛💛💛💛

Anonymous
Not applicable

Hi  @JesusPenelas ,

 

Here are the steps you can follow:

1. Create measure.

Flag =
var _select=SELECTCOLUMNS('DimCalendar',"MonthID",[Month_ID])
var _table=
FILTER('Fact_LFL_Store','Fact_LFL_Store'[Month_ID] in _select && [LFL_YN]<>1)
var _column=SELECTCOLUMNS(_table,"Date",[STORE_ID])
return
IF(
   NOT( MAX('FactSales'[STORE_ID])) in _column && MAX('FactSales'[Month_ID]) in _select,1,0)
Sum_Measure =
SUMX(ALLSELECTED('FactSales'),[SALES])

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_0-1669964318565.png

3. Result:

Select 202208 202209 202210:

vyangliumsft_1-1669964318567.png

Select 202207 202208 202209:

vyangliumsft_2-1669964318569.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.