Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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 !!
Proud to be a PBI Community Champion
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
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
Thanks for reply
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |