topic Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection in DAX Commands and Tips

Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Wed, 24 Sep 2025 15:44:11 GMT

I have the delow table

and a measure like this

SO SENT =

VAR CurrentCustomer = SELECTEDVALUE('Sell out'[CustomerCode])

VAR YearsToCheck = {2024, 2025}

-- Get selected month numbers from slicer (e.g., Feb=2, Mar=3, etc.)

VAR SelectedMonths =

CALCULATETABLE(

VALUES('Date'[Month]),

KEEPFILTERS('Date')

)

-- Exit early if no customer

VAR IsCustomerBlank = ISBLANK(CurrentCustomer)

-- Calculate total combinations to check (e.g., 4 months * 2 years = 😎

VAR MonthCount = COUNTROWS(SelectedMonths)

VAR ExpectedCount = MonthCount * 2

-- Generate all combinations (e.g., Feb-2024, Feb-2025, ..., May-2025)

VAR MonthYearPairs =

GENERATE(

SELECTCOLUMNS(YearsToCheck, "Year", [Value]),

SELECTCOLUMNS(SelectedMonths, "Month", [Month])

)

-- Loop through combinations and count where customer has Qty > 0 (excluding day 15)

VAR ValidCombinations =

COUNTROWS(

FILTER(

MonthYearPairs,

VAR ThisYear = [Year]

VAR ThisMonth = [Month]

VAR MatchQty =

CALCULATE(

SUM('Sell out'[Qty]),

FILTER(

ALL('Sell out'),

'Sell out'[CustomerCode] = CurrentCustomer &&

YEAR('Sell out'[Date]) = ThisYear &&

MONTH('Sell out'[Date]) = ThisMonth &&

DAY('Sell out'[Date]) = 1 &&

'Sell out'[Qty] > 0

)

RETURN

NOT ISBLANK(MatchQty) && MatchQty > 0

)

RETURN

IF(

IsCustomerBlank,

"SO Not REC",

IF(

ValidCombinations = ExpectedCount,

"SO REC (Full Month)",

"SO Not REC"

)

I did all the steps creating a table like below

SO Status Slicer =

DATATABLE("SO Status", STRING, {

{"SO Not REC"},

{"SO REC (Full Month)"}

})

and then fiter ussing the below

SO Status Match =

VAR StatusFromMeasure = [SO SENT]

VAR SelectedStatuses =

VALUES('SO Status Slicer'[SO Status]) -- supports multi-select too

RETURN

IF(

StatusFromMeasure IN SelectedStatuses,

)

but there are two issues first of all is not efficient and second when I an adding a new field to the visualisation table the numbers are changing the fields are taken from related table could you please help on that?

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

lbendlin — Wed, 24 Sep 2025 23:23:02 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Thu, 25 Sep 2025 04:26:28 GMT

Hi here is an example below

So suppose that I have data for the 3 month above for Jan, Feb and March and for the company C I do not have data for January 2024, for company A I do not have data for February 2024 and for the company E I do not have data for March 2025, so when the user selected these 3 months and for the filter the selection SALES RECEIVED I would like to to be appeared only the companies B and D and their cumulative data. So the companies that I have data for both 2024 and 2025 for the month selection. Thank you

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Thu, 25 Sep 2025 04:29:20 GMT

and then for these companies I would like to add to the visualization table a field like the products that they are selling and the number be correct and do not change.

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Thu, 25 Sep 2025 04:38:09 GMT

Explanation

Company C is missing data for January 2024.

Company A is missing data for February 2024.

Company E is missing data for March 2025.

So, when filtering Jan + Feb + Mar for SALES RECEIVED, I only want to see companies that have data in both years (2024 & 2025) for all selected months.

Expected Result (Cumulative)
Company | Total Sales 2024 | Total Sales 2025
------------------------------------------------
Company B | 277 | 136
Company D | 39 | 3

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

johnt75 — Thu, 25 Sep 2025 10:06:23 GMT

If you have a date table linked to 'Sell Out'[Date] and the slicers for year and month are both coming from the Date table, you can create a measure like

SO Sent = IF ( HASONEVALUE ( 'Sell out'[CustomerCode] ), VAR DatesAndValues = ADDCOLUMNS ( VALUES ( 'Date'[Year month] ), "@sales", CALCULATE ( SUM ( 'Sell out'[Qty] ) ) ) VAR NumMonths = COUNTROWS ( DatesAndValues ) VAR NumMonthsWithSales = COUNTROWS ( FILTER ( DatesAndValues, [@sales] > 0 ) ) VAR Result = IF ( NumMonths = NumMonthsWithSales, "SO REC (Full Month)", "SO Not REC" ) RETURN Result )

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

V-yubandi-msft — Thu, 25 Sep 2025 10:13:32 GMT

Hi Panos_12 ,

Thank you for clearly outlining the scenario, it helped me understand your requirements.

You want to display only companies with complete sales data for both 2024 and 2025 across all selected months, such as January, February, and March, and ensure the results remain consistent even when fields like Product are added to the visual.

I’ve attached the Power BI file for your reference. Please let me know if you need any changes or improvements.

Best regards,

Yugandhar.

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Thu, 25 Sep 2025 15:11:26 GMT

thank you but still not efficient my logic is more than the below

SO SENT =

VAR SelectedMonths = VALUES('Date'[Month])

VAR YearsToCheck = {2024, 2025}

VAR CurrentCompany = SELECTEDVALUE('Sell out'[CustomerCode])

VAR MonthYearPairs =

GENERATE(

SELECTCOLUMNS(YearsToCheck, "Year", [Value]),

SelectedMonths

)

VAR ValidCount =

COUNTROWS(

FILTER(

MonthYearPairs,

VAR ThisMonth = [Month]

VAR ThisYear = [Year]

VAR Qty =

CALCULATE(

SUM('Sell out'[Qty]),

FILTER(

ALL('Sell out'),

'Sell out'[CustomerCode] = CurrentCompany &&

'Sell out'[MonthColumn] = ThisMonth &&

'Sell out'[YearColumn] = ThisYear &&

Day('Sell out'[Date]) <> 15 &&

NOT(ISBLANK('Sell out'[Qty]))

)

RETURN Qty > 0

)

VAR ExpectedCount = COUNTROWS(SelectedMonths) * 2

RETURN

IF(ValidCount = ExpectedCount, 1, 0) but in terms of resources is very demanding for large tables also the Date and the Sell Out tables are related. Finally, I would like this measure to be converted to a slicer to be added to the report. I think that is a very complicated issue

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Fri, 26 Sep 2025 06:33:23 GMT

Thank you but the above is not filtering correctly the companies

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

V-yubandi-msft — Fri, 26 Sep 2025 07:17:28 GMT

Hi Panos_12 ,
Thanks for sharing. I agree with your approach. While your current logic works, the GENERATE + ALL scan can be resource intensive. You might consider using a YearMonth key to compare counts, or pre-flagging completeness in Power Query. This would make your measure lighter and more efficient.

Regards
Yugandhar.

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

V-yubandi-msft — Mon, 29 Sep 2025 10:27:53 GMT

Hi Panos_12 ,
Could you let us know if your issue has been resolved or if you need any further assistance.

Thank you.

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

Panos_12 — Mon, 29 Sep 2025 10:32:29 GMT

No I still looking for an answer this issue has not been resolved. I need an answer that will not requires so much resourses because the code I have developed works but needs but it is noe efficient.

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

V-yubandi-msft — Fri, 03 Oct 2025 10:48:46 GMT

Thank you for providing the update. I have attached the updated PBIX file for your review. I hope it aligns with your requirements. When you have a moment, please take a look and let me know if everything appears satisfactory or if there are any changes you would like me to make. Your feedback will help ensure the file meets your expectations.

Regards,
Yugandhar.

Re: Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

V-yubandi-msft — Wed, 08 Oct 2025 06:19:12 GMT

Hi Panos_12 ,
When you have a moment, please review my latest response along with the attached PBIX file and let us know if any changes are needed.