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.
I want to remove some duplicates from a calculated column. I want to use this column in a measure in LookupValue() but I get an error "a table of multiple values was supplied where a single value was expected." Upon studying the calculated column I got to know that some rows have duplicates which prevent me from getting the max value in the lookupvalue function.
Solved! Go to Solution.
Hi @Zain26,
Create measures like below:
Max Count = CALCULATE ( MAX ( 'TimeRange Table'[CountofTimeRange] ), ALLEXCEPT ( 'TimeRange Table', 'TimeRange Table'[USER] ) ) Peak Time Range = CALCULATE ( LASTNONBLANK ( 'TimeRange Table'[TimeRange], 1 ), FILTER ( 'TimeRange Table', 'TimeRange Table'[CountofTimeRange] = [Max Count] ) )
Add field [USER] into Columns area of matrix, and add measure [Peak Time Range] into Values section.
Best regards,
Yuliana Gu
Hi guys, I am trying to get the most repeated value in a column grouped against another column.
USER | TimeRange | CountofTimeRange|
BANK 9pm-12am 54
BANK 6pm-9pm 24
BANK 6pm-9pm 24
BANK 3pm-6pm 54
STORE 9pm-12am 96
STORE 12pm-3pm 34
STORE 6pm-9pm 78
STORE 3pm-6pm 10
SCHOOL 9am-12pm 5
SCHOOL 12pm-3pm 120
SCHOOL 6pm-9pm 1
SCHOOL 3pm-6pm 2
==================================
I want to get the value 9pm-12am against th BANK column, in a row using matrix visualization. and ignore the lesser time range. However when I attempt to do this in a measure using
VAR peaktime= Calculate(LookupValue([Timerange],[CountofTimeRange],MAX([CountofTimeRange])),[Date].[month]>= DATE(maxyear,maxmonth,1))
receive an error which says
A table of multiple values was supplied where a single value was expected.
Using the Matrix visual the result should be something like this,
BANK STORE SCHOOL
PeakTime 9pm-12am 9pm-12am 12pm-3pm
Hi @Zain26,
Create measures like below:
Max Count = CALCULATE ( MAX ( 'TimeRange Table'[CountofTimeRange] ), ALLEXCEPT ( 'TimeRange Table', 'TimeRange Table'[USER] ) ) Peak Time Range = CALCULATE ( LASTNONBLANK ( 'TimeRange Table'[TimeRange], 1 ), FILTER ( 'TimeRange Table', 'TimeRange Table'[CountofTimeRange] = [Max Count] ) )
Add field [USER] into Columns area of matrix, and add measure [Peak Time Range] into Values section.
Best regards,
Yuliana Gu
Hi,
For Bank, there are two time period against the maximum "CountofTimeRange". So the answer should be 9pm-12am,3pm-6pm. Am i correct?
Hi,
I think i have solved it. See the image below. Is that the result you are expecting?
You posted a similar question here. Now please tell me which of the two questions is your actual one - the one in this thread or the other one. The major difference between both datasets is that the one in the other link does not have any count column (you have inface created that in a calculated column). So, my question is that in your base data, will you actually have a count column or no.
I have also been able to solve the probelm where you do not have a count column. See imag below
Hi,
You may refer to my solution here.
Hope this helps.
Hi,
Please give me time until tomorrow to share my solution.
@Zain26 put the filter Visual Level filter it will work so that you can ignore the lesser time
I have other measures as well in the Matrix would the filter affect them too? Also please advise a few.
Also please tell about how to deal with the error?
I am pretty new to powerBI so I'd be really thankful for your help.
What formula did you use for the column?
Occurence =
VAR latestDateTime= MAXA('Comments'[Time])
VAR year= YEAR(latestDateTime)
VAR month= MONTH(latestDateTime)
VAR c= CALCULATE(COUNTROWS('Comments'),ALLEXCEPT(Comments,Comments[Intervals],Comments[CommentIdentifier]),Comments[Time].[Date]>=DATE(year,month,1))
return c
Here Intervals is another calculated table where I have defined the time ranges using my Time column.
Basically I want to get the Interval value against max time for each Comment Identifier. But there are duplicates as you can see for Interval 3pm-6pm Occurance is 40, and for 9pm-12am is also 40.
This is how I want my matrix to look like. If you look at the Status Peak , that is how I expect to get the value for CommentP. In simple words I expected to take the most repeated value in the Interval column. Upon to do that I tried this approach of getting count for each [Interval] against the [Comment Identifier]. And then look for the max count and retrieving the corresponding [Interval]
Hi,
I am still not clear. With respect to the data that you have shared, please show how you want your matrix visual to be (with actual entries in rows/column/values)
The image which I shared is exactly the way I want my visualization to be. The [CommentIdentifier] is the columns while all the measures are the values which are showed on rows.
I intend to create a measure to calculate the most repeated value of [Interval] for each [CommentIdentifier] and then add it to the matrix as a value and then show it on row using the provided functionality.
This is how I would like the value Comment Peak to be. Currently I have filled it with dummy values. The actual values would vary for each column.
Hi,
You posted a similr question here as well. See my reply there.
Hi,
In a spare column, please indicate the values that you are expecting.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |