How do i convert dimensions into power bi DAX measure

[saivina2920]

I am using below dimension statement in some other report. 

Now i just want to convert into measure in DAX. 

How to do this.

Dim1

=If(vUserInput, if(IsNull(EMP_RELIEVE_DATE) AND [EMP_STATE]='$(vEmPlaces)', date(EMP_RESIGN_DATE),date(EMP_RELIEVE_DATE)), if(not IsNull(EMP_RELIEVE_DATE) AND EMP_RELIEVE_DATE <= floor(Today()) AND [EMP_STATE]='$(vEmPlaces)', EMP_RELIEVE_DATE))

[amitchandak]

@saivina2920 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Are you trying SCD ?

Refer video from Guyinacube -https://www.youtube.com/watch?v=tKeaQpWynzg

[saivina2920]

Thanks for your reply.

First Step ==> when user select emp_state in slicer (Ex : EMP_STATE = "Gujarat"),

EMP_STATE EMP_STATUS EMP_RESIGN_DATE EMP_RELIEVE_DATE
Gujarat Working 17/02/2021 17/02/2021
Gujarat Working 18/02/2021 ====> blank
Gujarat Working 19/02/2021 19/02/2021

Second Step ==> If found any blank available in the field of EMP_RELIEVE_DATE,

Third Step ==> then "get the user input" value + add in EMP_RESIGN_DATE.
(Ex : 5 (User Input) + 18/02/2021(EMP_RESIGN_DATE) ==> now for the blank value of EMP_RELIEVE_DATE is "23/02/2021"

Fourth Step : Like that, all the blank cells have to calculate by the system and apply in the exist formula of <10 days, 10 to 20 Days and > 20 Days.

NOTE : IF NOT NULL IN EMP_RELIEVE_DATE, then the system should calculate "Without user input" as mentioned the periods <10 days, 10 to 20 Days and > 20 Days.

For Reference 

vToday = TODAY()
vCalDate10 = EMP_TABLE[vToday] - 10
vCalDate20 = EMP_TABLE[vToday] - 20

<10Days =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] >= (EMP_TABLE[vCalDate10]) && EMP_TABLE[EMP_RESIGN_DATE] <= (EMP_TABLE[vToday]) && EMP_TABLE[EMP_STATUS] = "Working"))

10-20Days =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] >= (EMP_TABLE[vCalDate20]) && EMP_TABLE[EMP_RESIGN_DATE] <= (EMP_TABLE[vCalDate10]) && EMP_TABLE[EMP_STATUS] = "Working"))

>20Days =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] <= EMP_TABLE[vCalDate20] && EMP_TABLE[EMP_STATUS] = "Working"))

https://1drv.ms/u/s!AiSRcgO5FUmN8SJ915GrNWQlwf1i?e=Hcc8ip 

[Anonymous · Accepted solution]

HI @saivina2920,

You can try yo use the following measure formula to replace the blank parts and use aggregate function to summary these replaced values:

Measure =
VAR selected =
    MAX ( Table[Selection] )
VAR summary =
    SUMMARIZE (
        EMP_TABLE,
        [EMP_STATE],
        [EMP_STATUS],
        [EMP_RESIGN_DATE],
        "EMP_RELIEVE_DATE",
            IF (
                EMP_TABLE[EMP_RELIEVE_DATE] <> BLANK (),
                [EMP_RELIEVE_DATE],
                [EMP_RESIGN_DATE] + selected
            )
    )
RETURN
    COUNTX (
        FILTER ( summary, EMP_TABLE[EMP_STATUS] = "Working" && 'Date filter' ),
        EMP_TABLE[EMP_NO]
    )

Regards,

Xiaoxin Sheng

[saivina2920]

Thanks. it's working fine. There is no "Accept as Solution" button in your follow up.

[saivina2920]

what is "Date filter"

[Anonymous]

HI @saivina2920,

It means to use your 'date filter' expressions to replace that part.
Regards,

Xiaoxin Sheng

[saivina2920]

pls. let us know if you need any more details.

[Ashish_Mathur]

Hi,

The null cell will have to be plugged in via a calculated column formula.  The problem is that calculated column formulas do not refresh when a filter/slicer is selected.  So if the 5 can be kept as a constant, then we can solve this question.

[saivina2920]

can you give us the samples...?