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.
So here is detail,
Table 1:
Enhance -- Column Name
4773
Table 2:
Sustain -- Column name
446
Table3:
ETE TAT -- Column Name
1
Now, Current Output table is like below; (As you can see Score column is empty)
Name Score
USA
UK
Canada
Now i would like to apply CASE statement (T-SQL) here by using SWITCH or any other function;
Final OUTPUT Table should be like below;
Name Score
USA 4773
UK 446
Canada 1
I am getting like this after applying SWITCH function;
Name Score
USA 1241
UK
Canada 1
What i found out is from Table1: First row is coming not total becuase when i pull one category and Enhance fields into TABLIX format the table look like;
Category Enhance
Null 1241
WWC 30
REC 123 and so on.....i feel like its only fetching first row not total.
Thanks
Solved! Go to Solution.
Hi @damit23183 ,
First you need to create two measures to calculate the sum of "enhance" and "sustain",
then use the "switch" function to create a new measure and drag it into the table
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I do not think this the complete info. But assume table 4 has a country name, you can do like
Score = if(table4[Name] ="USA" , maxx(Table 1,Table 1[Enhance]), if(table4[Name] ="UK",maxx(Table 2,Table 2[Sustain]),
maxx(Table 2,Table 2[ETE TAT])))
Switch true, you can also use it.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
Hi,
This is orginal table where score has calcualted for Enhance and Sustain which you can see in highlighted below;
Now, after Case statement i would have these result 3576 and 447 in line of highlighted KPI name below;
After applying SWITCH i am not getting anything here, also please one note here there is no relationship between these two tables.
Therefore, only with CASE statement i can pull the value here.
Hope this will be enough information.
Thanks in Advance!
Thanks
Hi @damit23183 ,
First you need to create two measures to calculate the sum of "enhance" and "sustain",
then use the "switch" function to create a new measure and drag it into the table
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
THank you for your response and it worked great.
However, have another question on how to find count, sum, average by category.
I.e Count (ID) by Service only. Just like doint Count by Group in SQL.
i would like to do Average, Sum and Count by Category in PowerBI Desktop.
Thanks
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |