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 all,
Need some help calculating the month of use.
I have part number column and 12 additional columns for consumption for each month.
For example, Part number X1234 and the consumption in month 1, month 2... month 12.
The consumption can be negative (more returns than issues), 0 or positive.
I need to add a column to count the number of months with consumption greater than 0.
Very simple to do it in excel with countif but somehow I couldn't find the equivalent in DAX.
Thanks in advanced
Solved! Go to Solution.
Your first issue is the shape of your data. The data engine for Power BI is Power Pivot. This is a column-based database, essentially. So, you would first need to "un-pivot" your data via Power Query ("Get Data" in power BI) so that you would go from having a 13 column table to a three column table with Part number, month, and value.
Once you've done that, you could write a dax measure to do the count for you. Here I'll need to defer to some dax ninjas in the group. But it would likely involve a variable that would countrows on a dax query, then wrapping a count function in a calculate with that variable equals 1. If I can find an example of the formula, i'll update this post.
Your first issue is the shape of your data. The data engine for Power BI is Power Pivot. This is a column-based database, essentially. So, you would first need to "un-pivot" your data via Power Query ("Get Data" in power BI) so that you would go from having a 13 column table to a three column table with Part number, month, and value.
Once you've done that, you could write a dax measure to do the count for you. Here I'll need to defer to some dax ninjas in the group. But it would likely involve a variable that would countrows on a dax query, then wrapping a count function in a calculate with that variable equals 1. If I can find an example of the formula, i'll update this post.
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 |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |