Log In
Register
Home
New Member
Power Pivot
January 28, 2020
Offline
Hello, my name is Zac first time poster here. I took Mynda's PowerPivot class and it helped me get to where I am today! I am working on a capacity planning tool for my organization and I need help figuring out how to get a measure to display a text value.
In a nut shell I need help getting my row label value to be populated in a measure so I can perform calculations on it. In the image below you can see what my row labels are in my pivot table.
Below I have an example of my desired pivot table end result. The first measure I need to make illustrated by the *Measure* Host Name column is just an exact copy of the row label which is also host name. I have attempted to use a measure with this formula
=ConcatenateX(Host_Data,Host_Data[Host_Name],"",)
However; when I try and add this measure to the values section of my pivot table nothing happens. I think the ConcatenateX fuction I built makes logical sense, but I don't understand why I can't add it to my Pivot table values section when I drag it in there nothing happens. Do you think it is because I have two rows in the row section?
The second measure I need to capture is going to be the last digit of the host name which is generation number. I am pretty sure I can figure that out on my own with a simple trim function.
The third measure I need to make is going to be a measure that will produce a value depending on the generation number, in this instance you get a multiplier of 2 for generation 5 & 6 and a multiplier of 3 for generation 7 & 8. I might need some help figuring out the most efficient way to do this, but I need to get measure 1 and 2 built first.
July 16, 2010
Offline
Hi Zac,
Welcome to our forum! It's great to hear you're making use of your Power Pivot skills 🙂
Your formula looks like the last comma is in the wrong place:
=ConcatenateX(Host_Data,Host_Data[Host_Name],"",)
If you want the delimiter to be a comma then it should be:
=ConcatenateX(Host_Data,Host_Data[Host_Name],",")
In regards to your third measure, I'd use a dimension table to lookup the multiplier.
Mynda
New Member
Power Pivot
January 28, 2020
Offline
Hey Mynda!! I had intended for there to be no delimiter because I will only ever have 1 value that I want produced. Even if I try your formula when I go to move the measure to the values section nothing happens. =ConcatenateX might not be the best formula for my goal. At the end of the day I just need a measure to produce for me the same text that is in my row column. I have been playing with this all morning and I am still stumped.
July 16, 2010
Offline
Hi Zac,
The delimiter argument is optional, so you could just leave it out. That said, if the purpose of adding this column is to detect what the last two digits are and then lookup the multiplier, then I would do this with a dim table. e.g. create another table with the following columns:
-Host name
-Last two digits of host name
-Multiplier
Then create a relationship between the dim and fact table so you can reference the multiplier in your DAX measure.
Mynda
1 Guest(s)
