Forum Discussion
Stuck with DAX - need to replicate Excel formula
I'm stuck trying to replicate an Excel formula in Power BI and I'm completely stumped.
This is what it looks like in Excel:
TEXTJOIN(", ",TRUE,IF((data1=SelectedDropdown) * (data2=SelectedDropdown2),""))
Any insight would be appreciated; thank you!
If you click 'Transform Data' in the sample file I sent through, you'll notice that I've used your current data collection format and transformed it for you, so up to you if you change the collection method or not - let me know if you still are getting stuck, otherwise hope there's a solution in one of these replies for you 🙂
15 Replies
mpbems It looks like you need to reference value of slicers?
Power BI measures will do this, columns will not. Unfortunately you need to be in a column for this measure, so can you provide more info about how you want to use it?
I think something like this will work:
Measure =
MAXX(Table,
COMBINEVALUES ( ", ", IF( (data1= SELECTEDVALUE ( Table[SelectedDropdown] ) ) * (data2=SelectedDropdown2),"")I'm not sure what your IF statement is doing or what your SELECTED dropdowns are but hopefully I've given you enough clues to get started, report back with any issues.
AllisonKennedy when I enter the code, it looks like I'm a parenthesis or two short.
NamesMon =MAXX(FieldSchedInput,COMBINEVALUES(", ",IF(([Week]=SELECTEDVALUE(DateByWeek[Week])) * ([SearchMon1]=SELECTEDVALUE(MBRDN[Name1]),"")mpbems Sorry, that was just meant to be a sample.
Is the data you provided the raw data? Or can you get it in a different format?
If it's the raw data, I would unpivot it first in Power Query - Unpivot the other columns after selecting the Visitor and Week Column.
Then Split column > by non-digit digit to remove the numbers from the days of week.
Finally, use one of my favorite functions, CONCATENATEX:
Visitors =
CONCATENATEX( Table, Table[Value], ", ")Put that in a matrix with Attribute in the Columns and Week in the rows. Add your slicers for Location.
You can rename Attribute and Value to better names that make sense to you of course. 🙂
At some stage you may consider getting dimension tables, but we'll leave that for another post.
Hi, mpbems
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Data from Visitor through Saturday2 columns appear the exact same way in the source (Sharepoint list).
Visitors can select more than one location per day, which is why there are two columns per day of week.
When I select a week and location, I'm looking to see all visitors scheduled for each day, whether it's their first or second stop.
There are a few hundred location IDs and maybe around 100 visitors but I only added a few to show how it works currently in Excel.
Hi,
Does this measure work?
Measure = selectedvalue(Data1[text1])&", "&selectedvalue(Data2[text1])
If not, then share some data (in a format the can be pasted in an MS Excel file) and show the expected result.
Ashish_Mathur I've updated the info I shared in an earlier post. What I'm trying to do is create a visible schedule based on user input. In the example below, column A is a list of visitors and columns C through P represent locations they are scheduled to visit on a particular day. I'm looking to select a week, and a location, and view all scheduled visitors on that given day of the week.
Initially I had numbers listed but they should actually be locations.
Row 26 includes the slicers, and row 29 is the desired table layout (column headers in line 28).
Hi,
Share data in a format that can be pasted in an MS Excel file.
