March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
Does any one know how to calculate a column that has text and number for data?
I can calculate the results for the Column Overall Satisfaction using this DAX formula
but how would I write it for the Columns that have both text and number?
Survey ID | Overall Satisfaction | Spa Satisfaction | Pool Satisfaction |
1000001 | 10 | 9 | 10 |
1000002 | 9 | 8 | 10 |
1000003 | 10 | Did not use | 10 |
1000004 | 9 | Did not use | 9 |
1000005 | 6 | 8 | 2 |
1000006 | 8 | 10 | 7 |
1000007 | 9 | 8 | 9 |
1000008 | 8 | 6 | 9 |
1000009 | 9 | Did not use | 9 |
1000010 | 9 | 8 | 9 |
1000011 | 8 | 7 | 9 |
1000012 | 3 | 6 | 8 |
1000013 | 1 | 3 | Did not use |
1000014 | 5 | 9 | 4 |
1000015 | 8 | Did not use | 8 |
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but please check the below three measures, and the attached pbix file.
I think Overall Satisfaction Score measure also has to be written in a different way, like the below.
Please check if the below results are what you expected.
Overall Satisfaction Score =
DIVIDE (
CALCULATE (
COUNT ( 'Table1'[Overall Satisfaction] ),
FILTER ( 'Table1', 'Table1'[Overall Satisfaction] * 1 >= 9 )
),
CALCULATE (
COUNT ( 'Table1'[Overall Satisfaction] ),
FILTER ( 'Table1', 'Table1'[Overall Satisfaction] * 1 >= 1 )
)
)
Spa Satisfaction Score =
DIVIDE (
CALCULATE (
COUNT ( 'Table1'[Spa Satisfaction] ),
FILTER (
'Table1',
'Table1'[Spa Satisfaction] * 1 >= 9
&& 'Table1'[Spa Satisfaction] <> "Did not use"
)
),
CALCULATE (
COUNT ( 'Table1'[Spa Satisfaction] ),
FILTER (
'Table1',
'Table1'[Spa Satisfaction] * 1 >= 1
&& 'Table1'[Spa Satisfaction] <> "Did not use"
)
)
)
Pool Satisfaction Score =
DIVIDE (
CALCULATE (
COUNT ( 'Table1'[Pool Satisfaction] ),
FILTER (
'Table1',
'Table1'[Pool Satisfaction] * 1 >= 9
&& 'Table1'[Pool Satisfaction] <> "Did not use"
)
),
CALCULATE (
COUNT ( 'Table1'[Pool Satisfaction] ),
FILTER (
'Table1',
'Table1'[Pool Satisfaction] * 1 >= 1
&& 'Table1'[Pool Satisfaction] <> "Did not use"
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim Wow. This is what I need and couldn't achieve. Thank you so much for your guidance and solution.
Hi,
I am not sure if I understood your question correctly, but please check the below three measures, and the attached pbix file.
I think Overall Satisfaction Score measure also has to be written in a different way, like the below.
Please check if the below results are what you expected.
Overall Satisfaction Score =
DIVIDE (
CALCULATE (
COUNT ( 'Table1'[Overall Satisfaction] ),
FILTER ( 'Table1', 'Table1'[Overall Satisfaction] * 1 >= 9 )
),
CALCULATE (
COUNT ( 'Table1'[Overall Satisfaction] ),
FILTER ( 'Table1', 'Table1'[Overall Satisfaction] * 1 >= 1 )
)
)
Spa Satisfaction Score =
DIVIDE (
CALCULATE (
COUNT ( 'Table1'[Spa Satisfaction] ),
FILTER (
'Table1',
'Table1'[Spa Satisfaction] * 1 >= 9
&& 'Table1'[Spa Satisfaction] <> "Did not use"
)
),
CALCULATE (
COUNT ( 'Table1'[Spa Satisfaction] ),
FILTER (
'Table1',
'Table1'[Spa Satisfaction] * 1 >= 1
&& 'Table1'[Spa Satisfaction] <> "Did not use"
)
)
)
Pool Satisfaction Score =
DIVIDE (
CALCULATE (
COUNT ( 'Table1'[Pool Satisfaction] ),
FILTER (
'Table1',
'Table1'[Pool Satisfaction] * 1 >= 9
&& 'Table1'[Pool Satisfaction] <> "Did not use"
)
),
CALCULATE (
COUNT ( 'Table1'[Pool Satisfaction] ),
FILTER (
'Table1',
'Table1'[Pool Satisfaction] * 1 >= 1
&& 'Table1'[Pool Satisfaction] <> "Did not use"
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
127 | |
84 | |
69 | |
53 | |
44 |
User | Count |
---|---|
202 | |
106 | |
100 | |
64 | |
56 |