Weird kind of logic problem for Slider

[Rjesak]

This question is a bit complicated so I'll start with the data.

We have a table that includes these columns:

unique_seq	file_date	closure_date
111	8/20/2020	1/10/2021
123	9/20/2020
234		1/20/2021
456	1/20/2021
567		4/20/2021
678	2/20/2021
789	3/1/2021	3/20/2021
890	4/1/2021
901	4/20/2021

We have a fairly common calculation for "Pending". These are records which are open as of a given date. Where the selected date is dd, the logic is this:

if file_date is not null and file_date <= dd and (closure_date > dd or closure_date is null) then pending. (So records that don't have a file_date will never count as pending.)

The user wants a slider based on pending date. I don't know how to build this because we don't really have a column to relate it to.

Below is a sample of whether each record would count as pending based on different dates:

	table columns				slider date
unique_seq	file_date	closure_date		9/30/2020	1/20/2021	today
111	8/20/2020	1/10/2021		y
123	9/20/2020			y	y	y
234		1/20/2021
456	1/20/2021				y	y
567		4/20/2021
678	2/20/2021					y
789	3/1/2021	3/20/2021
890	4/1/2021					y
901	4/20/2021					y

The only thing I've been able to come up with is to create a date table with all possible dates in it to use as the slider and then somehow build the logic into that but I haven't been able to figure out how that would work.

Any suggestions would be appreciated!

[Jihwan_Kim]

Hi, @Rjesak 

Please check if the below picture and the sample pbix file's link down below is what you are looking for.

Pending Count =

CALCULATE (

COUNTROWS ( Data ),

FILTER (

Data,

AND( Data[file_date] <= MAX ( Dates[date] ), not ISBLANK( Data[file_date]))

&& OR( Data[closure_date] > MIN ( Dates[date]), ISBLANK(Data[closure_date]) )

)

)

https://www.dropbox.com/s/g0y3tcuwtwvfwrx/Rjesak.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Rjesak]

Not quite. They don't want a count - the result would show a table with the records that match. They also want a slider that would allow them to change the date - the slider would have a date and the table would be responsive so would show the records that are pending as of the date on the slider. I've been messing around with what you built and I still can't get it to work that way.

[jdbuchanan71]

@Rjesak 

Take a look at my attached example, I think it does what you are looking for.

[Rjesak]

That looks correct. How did you build it?

[jdbuchanan71]

I attached my sample .pbix so you can download it at look at it.

[Rjesak]

When I open it, the visual cannot be displayed. When I view details, this is the error and detail info.

Feedback Type:
Frown (Error)

Timestamp:
2021-04-07T20:08:01.9383765Z

Local Time:
2021-04-07T16:08:01.9383765-04:00

Session ID:
2e60f884-63bd-4850-8d0e-13d41863e42f

Release:
February 2021

Product Version:
2.90.782.0 (21.02) (x64)

Error Message:
MdxScript(Model) (17, 25) Calculation error in measure 'Table'[Show]: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

OS Version:
Microsoft Windows NT 10.0.18363.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528040]

Peak Virtual Memory:
38.4 GB

Private Memory:
531 MB

Peak Working Set:
719 MB

IE Version:
11.1411.18362.0

User ID:
bae0b16c-1867-41b0-bf5e-dfbb3c1ca3e4

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\rjesak\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot19dba5aa-4e42-4439-9f43-00437e8101b6.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\rjesak\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_NewWebTableInference
PBI_eimInformationProtectionForDesktop

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_JsonTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_narrativeTextBox
PBI_dynamicParameters
PBI_anomalyDetection
PBI_newFieldList
PBI_cartesianMultiplesAuthoring

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

Formulas:

section Section1;

shared Table = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYzLDcAwCEN34RwpmPxglqj7r1GStlKoxMF6fmZOAkCJNAv7CXtGxs6gK3kvxZkd/cZS6orLlsOurQf22q2Px67B7kOdyd8eah5LxkdLWKnx/oQwMkb4v/B1Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [unique_seq = _t, file_date = _t, closure_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"unique_seq", Int64.Type}, {"file_date", type date}, {"closure_date", type date}})
in
#"Changed Type";

[v-xulin-mstf]

Hi @Rjesak,

Is your issue solved?

The pbix works well in my environment.

Please check: Power BI Version: 2.91.884.0 64

Best Regards,

Link

[Rjesak]

Oh my goodness, I didn't see that. Sorry. Looking now!