Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
olimilo
Responsive Resident
Responsive Resident

Get the equivalent value when HASONEFILTER() is true

I have two tables, Bucket with the following data:

 

ClientOn-Time Window
ABC1
DEF2
GHI3
JKL4
MNO5
PQR6
STU7
VWX8
YZ9

 

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? 

1 ACCEPTED 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)

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

HI @olimilo

 

I think this calculated measure worked for me

 

 

Measure = IF(
	HASONEFILTER('Bucket'[Client]),
	--THEN---
	MAX('Bucket'[On-Time Window]),
	--ELSE---
	blank()
	)

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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:

 

ClientDateReceivedDateReported
ABC1/1/20161/4/2016
GHI1/2/20161/3/2016
ABC1/3/20161/7/2016
JKL1/4/20161/10/2016
MNO1/4/20161/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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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 again. I think you need to wrap a MAX function around your bucket [on time window]

Sorry am on my phone.

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.