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.
We have implemented a form that measures the level of stress, psicosocial risk and some others
and I have to make some charts with that info
but I'm getting stuck with some formulas
we have 4 possible answers for each questions (Always, Usually, Sometimes, Rarely and Never) each one has a value (0,1,2,3,4 on the same order) and I need to dived them into groups and those group ino antoher group
something like this
the "ITEM" field is the number of each question
ej. "Ambiente de Trabajo" cover from question 1 to 5, and "Condiciones peligrosas" are the question 1 and 3
So I need to count how many times per questions does the number 0 appears
the times that 1 appears etc ect
Question 1 ------------- Times
4 14
3 3
1 3
2 0
so then I cant sum the result and create a new column for "Condiciones del trabajo" and get the total
it is really confusing
the whole table is in this link
NOM-035
Hope you can help me
Solved! Go to Solution.
Hi @Anonymous ,
How about this:
let
Source = Excel.Workbook(File.Contents("D:\Power BI\Temp Testing Case\Calculate values from a form.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categoría", type text}, {"Dominio", type text}, {"Dimensión", type text}, {"Ítem", type any}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Categoría", "Dominio"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Ítem", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Ítem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ítem"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ítem", Int64.Type}})
in
#"Changed Type2"
In addition, if it won't involve returning two "1" in "11", you can refer to this post: DAX function to search for multiple values in strings and count the number of times each value occur.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
How about this:
let
Source = Excel.Workbook(File.Contents("D:\Power BI\Temp Testing Case\Calculate values from a form.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categoría", type text}, {"Dominio", type text}, {"Dimensión", type text}, {"Ítem", type any}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Categoría", "Dominio"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Ítem", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Ítem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ítem"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ítem", Int64.Type}})
in
#"Changed Type2"
In addition, if it won't involve returning two "1" in "11", you can refer to this post: DAX function to search for multiple values in strings and count the number of times each value occur.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |