Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

how to write dax query based on sql query?

Hi Team,   how to write below sql query in dax expression?   select title, hours  from table where title like '%gold%' or title like '%silver%'   we need to find calcualte hours based on gold o...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    Please refer to:

    Table =
VAR temptab =
    FILTER (
        ADDCOLUMNS (
            Table5,
            "Type", IF (
                ISERROR ( FIND ( "Gold", Table5[Title] ) ) = FALSE (),
                "Gold",
                IF (
                    ISERROR ( FIND ( "Silver", Table5[Title] ) ) = FALSE (),
                    "Silver",
                    BLANK ()
                )
            )
        ),
        [Type] <> BLANK ()
    )
RETURN
    GROUPBY ( temptab, [Type], "Total Hours", SUMX ( CURRENTGROUP (), [Hours] ) )

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks for reply

 

can you please help below requirement .

 

1. I need to find only Gold and silver records, not others records.

2.  Total Hours for(gold and silver) / Hours

 

Thanks

Kunuthuri

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

Please refer to:

Table =
VAR temptab =
    FILTER (
        ADDCOLUMNS (
            Table5,
            "Type", IF (
                ISERROR ( FIND ( "Gold", Table5[Title] ) ) = FALSE (),
                "Gold",
                IF (
                    ISERROR ( FIND ( "Silver", Table5[Title] ) ) = FALSE (),
                    "Silver",
                    BLANK ()
                )
            )
        ),
        [Type] <> BLANK ()
    )
RETURN
    GROUPBY ( temptab, [Type], "Total Hours", SUMX ( CURRENTGROUP (), [Hours] ) )

 

Best regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for reply