topic Re: Changing the context filter based inside a calculation in DAX Commands and Tips

Changing the context filter based inside a calculation

Mr_Robot0092 — Wed, 05 Apr 2023 23:52:25 GMT

Hello,

I have a dimension that have the status of client(Active,inactive,...) , and i have a fact table that contains all my client with the status id and date key.

The status of client can change every day.

I have a report with a slicer on date and also some other filter in other dimension.

I have a table that show me the count of my client of each status .

NB Client Status 17132 ACTIVE 1530 INACTIVE 152 N/A

What i want is calculate the NB Client of each status and Adding and New status called Last Day Inactive that show me the Nb Client that are inactive in my last Date of my filter.

Example :

In my slicer i selected two Date from 1/04/2022 to 10/04/2022 .

The idea is calculate the NB Client in my Last Date in my case 10/04/2022 and showing the value , i want something like as bellow :

NB Client Status 17132 ACTIVE 1530 INACTIVE 152 N/A 15 Last Day INACTIVE

What i did , i create Calculated table "DimStatusTR" based on my Dimension table like this :

UNION ( ROW ( "Status_Key", "99", "Status_Label", "Last Day Inactive" ), SUMMARIZECOLUMNS ( 'DimStatus'[Status_Key], 'DimStatus'[Status_Label] ) )

I did a relationship between this new dimension and my Fact table , after that , i created a measure like as bellow :

Var _InactiveLAstDay = CALCULATE( [NbClient], FILTER( Fact_Client, RELATED('DimStatusTR'[Status_Label]) = "Inactive" && Fact_Client[Date_Key] = MAX(Fact_Client[Date_Key]) ) ) Var _OtherStatus = [NbClient] Var _current = SELECTEDVALUE('DimStatusTR'[Status_Label]) Var result = if(_current ="Last Day Inactive",_InactiveLAstDay +0,_OtherStatus) Return result

The result that i get is like as bellow :

NB Client Status 17132 ACTIVE 1530 INACTIVE 152 N/A 0 Last Day INACTIVE

What i want is something like that

NB Client Status 17132 ACTIVE 1530 INACTIVE 152 N/A 15 Last Day INACTIVE

Any idea how can i do that ?

Thanks for help !

Re: Changing the context filter based inside a calculation

amitchandak — Thu, 06 Apr 2023 01:46:40 GMT

Mr_Robot0092 , First of all you need a date wise status for that. Now you can have column on meausre for what you need

Like the last status column

var _max = maxx(filter(Table, [NB Client] = earlier([NB Client]) && [Date] < Earlier([Date]) ) , [Date])

return

maxx(filter(Table, [NB Client] = earlier([NB Client]) && [Date] =_max ) , [Status])

for measure refer

Power BI Abstract Thesis: How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

Re: Changing the context filter based inside a calculation

Mr_Robot0092 — Thu, 06 Apr 2023 08:15:23 GMT

Hello amitchandak

Thanks for replying .I tried to do your code but doesn't work i this [NB Client] = earlier([NB Client]) i have this error the parameter is not not the correct type .

The [NB Client] is measure with Distinctcount(client_id)
Any idea ?