How to get the two fixed columns to update when filter by date?

[Emm]

Based on the previous post, I've modified the fixed table to include a Frequency column. I want the Minimum and Targe columns to update when filtering by date (with the date column coming from the database table, Table1).  Below is the DAX i'm using, but I'm not sure where I went wrong - both the Minimum and Target columns don't update when filtered by date. Please help! Thank you!!

Testing = VAR SourceName = SELECTEDVALUE(TargetData[Source])
VAR Frequency = SELECTEDVALUE(TargetData[Frequency])
VAR BaseMin = SELECTEDVALUE(TargetData[Minimum])
VAR BaseTarget = SELECTEDVALUE(TargetData[Target])

-- Get number of distinct days in the BatchData table (with selected date range)
VAR SelectedDates =
CALCULATETABLE(
VALUES(DateTable[Date]),
ALLSELECTED(DateTable)
)
VAR NumDays = COUNTROWS(SelectedDates)

-- Adjust multiplier based on Frequency
VAR Multiplier =
SWITCH(
TRUE(),
Frequency = "Daily", NumDays,

Frequency = "Business Days", DIVIDE(NumDays, 5, 0).
Frequency = "Weekly", DIVIDE(NumDays, 7, 0),
Frequency = "Monthly", DIVIDE(NumDays, 30, 0),
1
)

-- Calculate scaled Minimum and Target
VAR MinValue = BaseMin * Multiplier
VAR TargetValue = BaseTarget * Multiplier

VAR SoldValue =
CALCULATE(
COUNT(BatchData[BatchId]),
FILTER(BatchData, BatchData[Source] = SourceName)
)

RETURN
IF(
SoldValue = SoldValue < MinValue,
SWITCH(
TRUE(),
Frequency = "Monthly", "#008000",  -- Green
Frequency = "Weekly", "#FFFF00", -- Yellow
"#FF0000" -- Red for Daily
),
BLANK()
)

Table1 (from the database)

FileName	Items	Date	TotalNum	TotalSpoiled
ap20250228-01	Apple	3/4/2025	200	10
ap20250228-02	Apple	3/4/2025	150	30
p20250228-01	Pear	3/4/2025	45	3
p20250228-02	Pear	3/4/2025	100	10
p20250228-03	Pear	3/4/2025	100	10
Or20250228-01	Orange	3/4/2025	150	20
Or20250228-02	Orange	3/4/2025	100	10
Or20250228-03	Orange	3/4/2025	80	0
Or20250228-04	Orange	3/4/2025	65	0
Or20250228-05	Orange	3/4/2025	80	4
Or20250228-06	Orange	3/4/2025	90	5
Or20250228-07	Orange	3/4/2025	55	0
On20250228-01	Onion	3/4/2025	200	10
On20250228-02	Onion	3/4/2025	100	5
On20250228-03	Onion	3/4/2025	180	20

Table2 (fixed table)

Source	Frequency	Target	Minimum
Apple	Daily	2	3
Orange	Daily	7	4
Pear	Bussiness Days	3	2
Onion	Weekly	3	1
Pepper	Monthly	1	1

Result: 

Source	Frequency	Sold	Target	Minimum
Apple	Daily	2	4	3
Orange	Daily	7	5	4
Pear	Business Days	3	3	2
Onion	Weekly	3	2	1
Pepper	Monthly	0	1	1

When filtered by Date, the Target and Minimum values should change accordingly.  For example, for Apple: Minimum should change from 3 to 6 and the Target from 4 to 8 for 2 days; for 3 days, the Minimum should change from 6 to 9 and the Target from 8 to 12, and so on.  Also, if the Sold value is less than the Minimum should hightlighted with color. The DAX formula doesn't seem to be working as expected. I'd appreciate any help. Thank you in advance!

Result for 2 days:

Source	Frequency	Sold	Target	Minimum
Apple	Daily	7	8	6
Orange	Daily	14	10	8
Pear	Business Days	6	6	4
Onion	Weekly	3	2	1
Pepper	Monthly	0	1	1

[v-achippa]

Hi @Emm,

Thank you for reaching out to Microsoft Fabric Community.

As the super user mentioned that in your description you mentioned that Apple has a base Target of 4 but in your Table2 it is listed as 2. Could you please confirm which one is correct?

Also, since Table1 only contains data for one date (3/4/2025) could you clarify how you are calculating the Sold value over multiple days? If you are expecting results for a date range, it would be helpful if you could provide additional data rows for those dates so that we can look more into the issue and provide you the best solution possible.

Thanks and regards,

Anjan Kumar Chippa

[Emm]

Hi!

Sorry! Here is the corrected data as below.  To calculate the Sold value is counting the number of the FileNames.

Table1

FileName	Items	Date	TotalNum	TotalSpoiled
ap20250304-01	Apple	3/4/2025	200	10
ap20250304-02	Apple	3/4/2025	150	30
p20250304-01	Pear	3/4/2025	45	3
p20250304-02	Pear	3/4/2025	100	10
p20250304-03	Pear	3/4/2025	100	10
Or20250304-01	Orange	3/4/2025	150	20
Or20250304-02	Orange	3/4/2025	100	10
Or20250304-03	Orange	3/4/2025	80	0
Or20250304-04	Orange	3/4/2025	65	0
Or20250304-05	Orange	3/4/2025	80	4
Or20250304-06	Orange	3/4/2025	90	5
Or20250304-07	Orange	3/4/2025	55	0
On20250304-01	Onion	3/4/2025	200	10
On20250304-02	Onion	3/4/2025	100	5
On20250304-03	Onion	3/4/2025	180	20
ap20250305-01	Apple	3/5/2025	100	0
ap20250305-02	Apple	3/5/2025	80	5
ap20250305-03	Apple	3/5/2025	150	20
ap20250305-04	Apple	3/5/2025	100	10
p20250305-01	Pear	3/5/2025	60	10
p20250305-02	Pear	3/5/2025	130	5
p20250305-03	Pear	3/5/2025	100	10
Or20250305-01	Orange	3/5/2025	130	10
Or20250305-02	Orange	3/5/2025	120	10
Or20250305-03	Orange	3/5/2025	100	0
Or20250305-04	Orange	3/5/2025	80	0
Or20250305-05	Orange	3/5/2025	60	4
Or20250305-06	Orange	3/5/2025	90	5
Or20250305-07	Orange	3/5/2025	55	0
Or20250305-08	Orange	3/5/2025	50	5
On20250305-01	Onion	3/5/2025	200	20
On20250305-02	Onion	3/5/2025	100	5
On20250305-03	Onion	3/5/2025	180	15
ap20250306-01	Apple	3/6/2025	100	0
ap20250306-02	Apple	3/6/2025	80	5
ap20250306-03	Apple	3/6/2025	150	10
ap20250306-04	Apple	3/6/2025	100	5
p20250306-01	Pear	3/6/2025	60	10
p20250306-02	Pear	3/6/2025	130	5
p20250306-03	Pear	3/6/2025	100	10
Or20250306-01	Orange	3/6/2025	130	5
Or20250306-02	Orange	3/6/2025	120	3
Or20250306-03	Orange	3/6/2025	100	0
Or20250306-04	Orange	3/6/2025	80	0
Or20250306-05	Orange	3/6/2025	60	8
Or20250306-06	Orange	3/6/2025	90	5
Or20250306-07	Orange	3/6/2025	55	0

Table2 (Fixed Table)

Source	Frequency	Target	Minimum
Apple	Daily	4	3
Orange	Daily	5	4
Pear	Bussiness Days	3	2
Onion	Weekly	2	1
Pepper	Monthly	1	1

Result for 1 day:

Source	Frequency	Sold	Target	Minimum
Apple	Daily	2	4	3
Orange	Daily	7	5	4
Pear	Business Days	3	3	2
Onion	Weekly	3	2	1
Pepper	Monthly	0	1	1

Results for 2 days:

Source	Frequency	Sold	Target	Minimum
Apple	Daily	6	8	6
Orange	Daily	15	10	8
Pear	Business Days	6	6	4
Onion	Weekly	3	2	1
Pepper	Monthly	0	1	1

Result for 3 days:

Source	Frequency	Sold	Target	Minimum
Apple	Daily	10	12	9
Orange	Daily	22	15	12
Pear	Business Days	9	9	6
Onion	Weekly	3	2	1
Pepper	Monthly	0	1	1

Thank you!

[v-achippa · Accepted solution]

Hi @Emm,

Thank you for the response and providing the full dataset. Please make sure you have a proper DateTable in your model which includes continuous dates and columns like IsBusinessDay, MonthYear, etc. Please try to use these below DAX measures:

Sold Count:

Sold =

CALCULATE(

    COUNTROWS('Table1'),

    ALLEXCEPT('Table1', 'Table1'[Source])

)

Days Count:

Days Count =

VAR SelectedSource = SELECTEDVALUE('Table2'[Source])

VAR Frequency = LOOKUPVALUE('Table2'[Frequency], 'Table2'[Source], SelectedSource)

VAR Dates = VALUES('DateTable'[Date])

RETURN

SWITCH(

    TRUE(),

    Frequency = "Daily", COUNTROWS(Dates),

    Frequency = "Business Days", CALCULATE(COUNTROWS(Dates), 'DateTable'[IsBusinessDay] = TRUE),

    Frequency = "Weekly", INT(COUNTROWS(Dates) / 7) + IF(MOD(COUNTROWS(Dates), 7) > 0, 1, 0),

    Frequency = "Monthly", DISTINCTCOUNT('DateTable'[MonthYear]),

    BLANK()

)

Dynamic Target =

LOOKUPVALUE('Table2'[Target], 'Table2'[Source], SELECTEDVALUE('Table1'[Source])) * [Days Count]

Dynamic Minimum =

LOOKUPVALUE('Table2'[Minimum], 'Table2'[Source], SELECTEDVALUE('Table1'[Source])) * [Days Count]

Status for highlighting:

Highlight Alert =

IF([Sold Count] < [Dynamic Minimum], 1, 0)

We can display this in a matrix or table visual.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Emm,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Emm,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Emm,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Irwan]

hello @Emm 

what is the target for apple? is it 2 or 4?
because in your description, you said 4 but in your fixed table, it says 2.

also how do you calculate Sold value? it seems you only provided one day data in your table1.

Thank you.