Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Qlik Expression to DAX

HI experts,

 

Can anyone help me to convert the below Qlik Experssion to DAX. It will be great help. The highlighted Yellow one are the varaibles and explanation for the varaibles are also marked below.

Thank you!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous,

    You can try to use the following expressions if they are suitable for your requirement:

    Measure1 =
VAR currDate =
    MAX ( Table[Date] )
VAR scaleMil =
    SELECTEDVALUE ( Table[scaleMil] )
VAR lable =
    SELECTEDVALUE ( Table[scaleMillable] )
RETURN
    FORMAT (
        INT (
            SUMX (
                FILTER (
                    Table,
                    YEAR ( [Date] ) = YEAR ( currDate )
                        && MONTH ( [Date] ) = MONTH ( currDate )
                ),
                [Blance] * [ActualFlag] * [ExchgRate]
            ) / scaleMil
        ),
        "$#,##0.0"
    ) & lable

Measure2 =
IF (
    COUNTROWS ( ALLSELECTED ( Table[Column1] ) ) = 1
        || COUNTROWS ( ALLSELECTED ( Table[Column2] ) ) = 1,
    1000000,
    1000000000
)

Measure3 =
IF (
    COUNTROWS ( ALLSELECTED ( Table[Column1] ) ) = 1
        || COUNTROWS ( ALLSELECTED ( Table[Column2] ) ) = 1,
    "Mn",
    "Bn"
)

    Regards,
    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    You can try to use the following expressions if they are suitable for your requirement:

    Measure1 =
VAR currDate =
    MAX ( Table[Date] )
VAR scaleMil =
    SELECTEDVALUE ( Table[scaleMil] )
VAR lable =
    SELECTEDVALUE ( Table[scaleMillable] )
RETURN
    FORMAT (
        INT (
            SUMX (
                FILTER (
                    Table,
                    YEAR ( [Date] ) = YEAR ( currDate )
                        && MONTH ( [Date] ) = MONTH ( currDate )
                ),
                [Blance] * [ActualFlag] * [ExchgRate]
            ) / scaleMil
        ),
        "$#,##0.0"
    ) & lable

Measure2 =
IF (
    COUNTROWS ( ALLSELECTED ( Table[Column1] ) ) = 1
        || COUNTROWS ( ALLSELECTED ( Table[Column2] ) ) = 1,
    1000000,
    1000000000
)

Measure3 =
IF (
    COUNTROWS ( ALLSELECTED ( Table[Column1] ) ) = 1
        || COUNTROWS ( ALLSELECTED ( Table[Column2] ) ) = 1,
    "Mn",
    "Bn"
)

    Regards,
    Xiaoxin Sheng

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , We might not be a Qlik expert. So please provide the requirement.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MDX Experts,

    Could you convert below query to DAX .This will be very helpful 🙂

     

    =if('$(vWHELocation)' = 'TWI-WH',
    num(aggr(sum(if([opratwarehousewithexpiry.Bin Code] <> 'SHIPBIN',
    [opratwarehousewithexpiry.Remaining Quantity])),Items.No_,[opratwarehousewithexpiry.Expiration Date]))
    ,
    num(aggr(sum([polandwarehousewithexpiry.Remaining Quantity]),Items.No_,[polandwarehousewithexpiry.Expiration Date])))

     

  • Bhavana_HR's avatar
    Bhavana_HR
    Frequent Visitor
    3 years ago

    Hi Experts,

     

    Can anyone please help me in converting the below mentioned Qlik formula to power bi DAX formula

     

    If( if(Year=2013,avg([Act Scrap % of Rev.]),(Sum([Act Scrap % of Rev.])/sum(if(Year>='2017',[Act_Revenue_Scrap] * [Average Rate],[Act_Revenue_Scrap]))))<>0,

    if(Year=2013,avg([Act Scrap % of Rev.]),(sum(if(Year>='2017',if(Act_Revenue_Scrap<>'0',[Act. Scrap $] * [Average Rate]),if(Act_Revenue_Scrap<>'0',[Act. Scrap $])))
    /sum(if(Year>='2017',[Act_Revenue_Scrap] * [Average Rate],[Act_Revenue_Scrap])))),

    null()

    )

     

    Thank you