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 have two tables, Bucket with the following data:
Client | On-Time Window |
ABC | 1 |
DEF | 2 |
GHI | 3 |
JKL | 4 |
MNO | 5 |
PQR | 6 |
STU | 7 |
VWX | 8 |
YZ | 9 |
And Timeliness that is connected to the Bucket table. What I'm trying to do is, if the user selects a Client from the Slicer, the measure (or column) should return the equivalent On-Time Window value (eg: user selects GHI, it should return 3). I tried this measure, but it doesn't work since it returns a table of values (I need a scalar value).
Bucket = IF(HASONEFILTER(Timeliness[Client]), VALUES(Bucket[On-time Window]), 50)
Is it possible to do this without having to use a Column and use a measure instead?
Solved! Go to Solution.
Hi @Phil_Seamark, I think I got it:
measure = VAR OTW = CALCULATE( LOOKUPVALUE(Bucket[On-time Window], Bucket[Client], MAX(Timeliness[Client])) ) RETURN IF(HASONEFILTER(Timeliness[Client]), OTW, 5)
HI @olimilo
I think this calculated measure worked for me
Measure = IF( HASONEFILTER('Bucket'[Client]), --THEN--- MAX('Bucket'[On-Time Window]), --ELSE--- blank() )
So I tried it again, using the Bucket[Client] as the slicer and @Phil_Seamark's formula:
Measure = IF( HASONEFILTER('Bucket'[Client]), --THEN--- MAX('Bucket'[On-Time Window]), --ELSE--- blank() )
and it works. However, the rest of my vis are displaying blanks since the filter that was applied was from the other table. Is there something I can do to fix the rest of the vis?
Edit: Hi @Phil_Seamark, the first table Bucket has been shown earlier. The Timeliness would be something like this:
Client | DateReceived | DateReported |
ABC | 1/1/2016 | 1/4/2016 |
GHI | 1/2/2016 | 1/3/2016 |
ABC | 1/3/2016 | 1/7/2016 |
JKL | 1/4/2016 | 1/10/2016 |
MNO | 1/4/2016 | 1/6/2016 |
Essentially, it's just a way to track if the reports were submitted on time, and that there is a threshold that differs on a per client basis (with the default being 5).
For the rest of the Vis, check which table you are using the Bucket field on each axis
Hi @Phil_Seamark, I'm afraid I don't follow. Also, not sure exactly how the table relations actually work here. When I selected a Table vis with the Timeliness[Client] and Bucket[On-time Window] as fields, I got the correct values. Now, what I'm trying to achieve is to substitute that value onto this measure (Bucket[On-time Window]):
On Time % = DIVIDE(
CALCULATE(COUNTROWS('Timeliness'), FILTER('Timeliness', 'Timeliness'[ReportRecievedTAT] <= Bucket[On-time Window])), -- Bucket[On-time Window] should be equivalent to the window according to the selected client, or 5 if all clients are selected
CALCULATE(COUNTROWS('Timeliness'), FILTER('Timeliness', 'Timeliness'[ReportRecievedTAT] >= 0)) )
But this gives the error:
A single value for column 'On-time Window' in table 'Bucket' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Hi @Phil_Seamark, I think I got it:
measure = VAR OTW = CALCULATE( LOOKUPVALUE(Bucket[On-time Window], Bucket[Client], MAX(Timeliness[Client])) ) RETURN IF(HASONEFILTER(Timeliness[Client]), OTW, 5)
Hi Phil, unfortunately it did not work for me. For context, I am using a Card vis (with the Timeliness[Bucket] as the field) and Timeliness[Client] as the field for the single-selection Slicer) to test if I'm getting the correct values. When I try your formula:
Bucket = IF(HASONEFILTER(Bucket[Client]), MAX(Bucket[On-time Window]), BLANK())
I'm getting a blank value (since it's thinking that Bucket was not filtered). When I change it to Timeliness like so:
Bucket = IF(HASONEFILTER(Timeliness[Client]), MAX(Bucket[On-time Window]), BLANK())
I'm only getting the maximum value, regardless of which Client I select.
I have already created the 1:M relationship between Bucket and Timeliness using the Client column.
Oh sorry.
I thought you just had a single table that had two columns.
Are you able to show what the columns are in each table?
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |