Show rows based on selected date

[NMDGA]

Hi Team ,

Help / Guidance is deeply appreciated !

My requirement is as below :

1. I have a "Reporting date from" , " Reporting date to" and "schedule effective date"column in my fact table for each RIDs.

2. I want to show rows in table visual based on selected date ( selected date will be used in date slicer and is derived using "reporting date from" column in disconnected calendar table)

3. I have created a hide/show measure and put in the conditions base don the three dates on when to show a particula row.

4. In my table visual i have RIDs and selected date measure to indicate which date was selected. 

if i bring in the "reporting date from" column in the table visual the count of rows in fact is working fine which matches the no of rows when exported for hide/show =1 . but if i just bring RID and selecteddate measure the count in KPI is still 10 but no of row sin table visual where hide/show =1 is only 6. 

the primary reason why i dont want to include "reporting date from" in table viusal is users are getting confused by date beaciuse each RID has diff dates which satisfies my condition. 

can pls help to suggest some solution with only RID and selecteddate measure to show in table visual which matches the count in KPI.

expected result =10 rows with selected date measure which will be a uniform date in table visual with hide/show =1

current result = 6 rows with selected date measure in table visual with hide/show =1

attached is the sample data SS.

[ValtteriN]

Hi,

It seems the issue you have is the way visualize reportting_date_from. Let's take the following data as an example:

If we use MAX and test if the value is 1 category A will return 0. Similarly to your 11th and 16th dates for !CE2VDML

We can solve this with a filter measure. So le'ts take the filter and apply that with logical test.

First I created a slicer table similarly to your ReportingCal

Filter measure:

Measure 31 = IF(MAX('Table (41)'[Column1])=MAX('Table (40)'[Column1]),1,0)


Now when we apply  this measure as a filter and apply slicer following happens:
Here  the originally faulty row of category A now returns 1 as expected.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[NMDGA]

hi @ValtteriN  can please explain with dates , i tried using dates but the measure (measure 30 ) you suggested comapres it with value 1. 

current measures and single date slicer table

[ValtteriN]

Hi,

So with dates the measure 30 would be the [show_hide_report_view]. Measure 31 would be the like you have in your example, but keep in mind that there shouldn't be relationships between slicer and fact table. Additionally you need to apply the filter measure to your visual. So Measure 31 = 1 needs to be applied. The idea here is that if your test measure [show_hide_report_view] checks for date matches by applying the filter measure logic your table will only show rows with reporting_date = "slicer selection". In my Example by selecting 1 the row with Category = A & Column1 = 2 was filtered out. Because of this the test measure returns 1. In your case this would mean that the table shows only 11.1.2024 and thus the test measure is 1.

[bhanu_gautam]

@NMDGA , Try below steps

Firsr Create a Calculated Column
Create a calculated column in your fact table that evaluates to 1 or 0 based on your conditions. 

IsVisible = IF(
[Reporting date from] <= [Selected Date] &&
[Reporting date to] >= [Selected Date] &&
[Schedule effective date] <= [Selected Date],
1,
0
)

Then modify the Hide/Show Measure

Ensure your hide/show measure is using the calculated column:

HideShowMeasure = SUM([IsVisible])

 Use the Calculated Column in the Visual
In your table visual, use the RID and the selected date measure. Apply a visual-level filter to show only rows where IsVisible = 1.

If you need a measure to show the selected date uniformly, you can create a measure like this:

SelectedDateMeasure = SELECTEDVALUE('Calendar'[Reporting date from])
Applying Filters
Add the RID and SelectedDateMeasure to your table visual.
Apply a visual-level filter on IsVisible to show only rows where IsVisible = 1.

[NMDGA]

hi @bhanu_gautam , I am afraid we cannot use the selected value function in calculated column , hence when i try to create the calculated col as you suggested i am unble to get the result as everything is 0.