how to write dax query based on sql query?

[Anonymous]

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

[PattemManohar]

@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]

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 · Accepted solution]

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]

Thanks for reply