Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

QlikView Exrepssion to DAX

Hello All,

 

I am currenlty in a project where migrating qlikview report into power bi.

 

I have below three tables which has a KEY Column common between these tables.

 

 

All three tables are connected using KEY column

In my table visual, I am taking the fields from these tables.

 

here, EmpID, Empname should come from headcount table. and YEAR comes from Mastertable, and rest comes from the 3rd table.

 

here the calculation which i am trying to convertinto DAX is below QlikView Expression.

round(Sum(If(Year(Max_Period_in_data) = (InputVar -1) and Year(MONTH_END_DT) =  (InputVar -1), 
If((EMPLOYMENT_STATUS_CD <> 'A' and EMPLOYMENT_STATUS_CD <> 'T'),([Pay MAX Month]*(num(month(MONTH_END_DT)) - num(month(Max_Period_in_data)))), 
([Pay MAX Month]*(12 - num(month(Max_Period_in_data)

 

Here, basically, this expression giving me the results as 

Based on the year selection per say 2025, 

it should give me the results of 2024 total values from 3rd table column caled as Pay_MAX_Month

 

Here is the formula that i have written.

 

CALCULATE(
    ROUND(
        SUMX(
            FILTER(
                MasterTable,
                YEAR(MAX('3rd Table'[Max_Period_in_data])) = (SELECTEDVALUE(MasterTable[Proxy Year]) - 1) &&
                YEAR(MAX(HEADCOUNT[MONTH_END_DT])) = (SELECTEDVALUE(MasterTable[Proxy Year]) - 1)
            ),
            IF(
                NOT (MAX(HEADCOUNT[EMPL_STATUS]) IN {"A", "T"}),
                [_PayMAXMonth] * 
                (MONTH(MAX(HEADCOUNT[MONTH_END_DT])) - MONTH(MAX('3rd Table'[Max_Period_in_data]))),
                [_PayMAXMonth] * 
                (12 - MONTH(MAX('3rd Table'[Max_Period_in_data])))
            )
            
        ),
        0
    )
)

 

the exepcted result for one of the Emp ID as per the QlikView is  - 

 

But using above dax i am getting wrong results

 

Could you please help me with correcting the DAX formula.

 

Thanks,

Mohan V.

data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    1 year ago

    Please don't copy and paste your buggy DAX measures and expect us to fathom what you want. 😀😀😀

     

    Please do clearly give a function description of what you what.

    Provide input data as a table, not a screen print, so we can import and build a solution.

    (Preferably with url to OneDrive, Dropboc or Google with read access. Hide any private data).

    Also provide example desired output.

    Take care to use same table and field names on the input, output and description.

    Keep it simple and omit anything that is not required for the problem.

     

    The Master table and 3rd table have a 1:1 relationship.

    So I recommend that you use Power Querrty to merge them into single table.

    It keeps things simple and will perfom better.

     

    The Master table has 1:M relationship to the Headcount table and is by directional.
    That looks weird because the Master table is by Employee ID and the Headcount has grade.

    I would expcet a employee to only have one grade, or a date showing when they changed grade.  

     

    If you simplify the schema to 1 or 2 tabes and explain concisely what you are trying to achive,

    then I think you will get a better and quicker answer rather than posting buggy DAX.  

    Hope that make sense, thank you 😁