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 or silver?

 

can you please assist?

 

Thanks

Kunuthuri

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

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous As there is no sample data, here is the sample data I assumed...

     

     

    Now create a "New Column" as below:

     

    TitleNew = SWITCH(TRUE(),
                    SEARCH("Gold",[Title],1,-1)>0,"Gold",
                    SEARCH("Silver",[Title],1,-1)>0,"Silver",
                    "Other")

     

    The table now looks like...

     

     

    Now create a "New Table" in required format with total hours...

     

    GoldSilverHours = SUMMARIZECOLUMNS(SearchText[TitleNew],"TotalHours",SUM(SearchText[Hours]))

     

    So fiinally, the output looks like...

     

     

    Hope this helps !!

    • 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