Slicer filter based on status in fact table

[Jyaul1122]

Hello All,

Greeting,

I have dimentsion table Warning(unique EWID column, Project,Description, Raised Date ) and Fact table EBU Status(EWID,Record Date,Time Impact, Cost Impact and EBU Status) and those are related with EWID one to many relation.

And also Warning(Raised Date) is related with Calendar table.

Warning Dim table:

EWID	Project	Description	Raised
76	Project 1	Description11	5/1/2024
77	Project 2	Description12	10/1/2024
78	Project 2	Description13	9/1/2024
80	Project 3	Description14	5/1/2024
120	Project 4	Description15	6/1/2024
121	Project 5	Description1	6/1/2024
122	Project 6	Description2	6/1/2024
123	Project 7	Description3	6/1/2024
124	Project 8	Description4	6/1/2024
125	Project 9	Description5	6/1/2024
126	Project 1	Description6	6/1/2024
127	Project 2	Description7	6/1/2024
157	Project10	Description8	5/1/2024
158	Project10	Description9	5/1/2024
159	Project10	Description10	5/1/2024

and EBU Status Fact table:

EWID	Record Date	Time Impact	Cost Impact	EBU Status
126	11/30/2024			EBU
80	5/31/2024			EBU
80	7/31/2024			EBU
76	7/31/2024			EBU
125	12/31/2024			Not EBU
123	12/31/2024			Not EBU
121	12/31/2024			Not EBU
76	6/30/2024			EBU
122	6/30/2024			Not EBU
80	6/30/2024			EBU
77	10/31/2024			EBU
76	11/30/2024			EBU
80	11/30/2024			EBU
76	5/31/2024	2.5 Weeks	11 K	Not EBU
123	1/31/2025			EBU
120	6/30/2024	3 Weeks	0.4 M	EBU

Requirement: I would like to have commulative Description value based on month selection from slicer, for ex: if the month selected May-24 then Description value will be all from Jan-24 to May-24 and for June-24 : Jan-24 to Jun-24 and so on

So that I wrote the measures:

Desc_=

var max_=MAX('Calendar'[Date])

return

CALCULATE(MAX('Warning'[description]),REMOVEFILTERS('Calendar'),'Warning'[Raised]>=DATE(2024,01,01)&&'Warning'[Raised]<=max_)

also I would like to get EBU Status from EBU Status table based on current month selected from Slicer:

EBU Status_ =

CALCULATE(MAX('EBU Status'[EBU Status]),'EBU Status'[Record Date]=MAX('Calendar'[Date])).

Whenever I list the value in table is fine But when I tried to apply Slicer EBU Status then Its not applying correct filter.

Could you please help me, how can I short out ?

[danextian]

Hi @Jyaul1122 

What do you mean by cumulative description? Are you supposed to accumulate them but they're not numbers? Or show the rows that are from the begining of the year up to the month selected? If the latter, then you will need a disconnected calendar table as using a related one will show only the rows  within the selected range and then a measure to be used as a visual filter

DateFilter = 
CALCULATE (
    COUNTROWS ( Warning ),
    KEEPFILTERS (
        Warning[Raised] >= DATE ( 2024, 1, 1 )
            && Warning[Raised] <= MAX ( 'Calendar'[Date] )
    )
)

Please see the attached sample pbix.

[Jyaul1122]

Accumulative is fine but I would like filter based on the EBU Status slicer, means EBU selected means only row with EBU for that selected month. For example when I applied slicer , result  will be only blue tick:

anothe example for Jun 24:

waiting for your reply, thanks.

[danextian · Accepted solution]

You can just uncheck Show items with no Data as EBU status is a measure and all other fields in the table are dimensions.